Parse SOAP XML Response in VBA

Question

We are trying to parse the below response and get the value "123456" from "result" tag using VBA Code but we are not getting anything:

Response Received:

------=_Part_119884_965967558.1620391101235
Content-Type: application/xop+xml;charset=utf-8;type="text/xml"
Content-Transfer-Encoding: 8bit
Content-ID: <e119e24d-e0b6-4ceb-a00a-dd094ef91ef7>

<?xml version="1.0" encoding="utf-8" ?>
<env:Envelope
    xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:wsa="http://www.w3.org/2005/08/addressing">
    <env:Header>
        <wsa:Action>http://xxxx</wsa:Action>
        <wsa:MessageID>urn:uuid:xxxx</wsa:MessageID>
    </env:Header>
    <env:Body>
        <ns0:uploadFileResponse
            xmlns:ns0="http://xxxx">
            <result
                xmlns="http://xxxx">123456
            </result>
        </ns0:uploadFileResponse>
    </env:Body>
</env:Envelope>

------=_Part_119884_965967558.1620391101235--

VBA Code:

    Dim xmldoc As Object
    Dim xmlnode As Object
    Set xmldoc = CreateObject("Microsoft.XMLDOM")
    xmldoc.SetProperty "SelectionLanguage", "XPath"
    xmldoc.async = False
    xmldoc.LoadXML .ResponseText
    For Each xmlnode In xmldoc.SelectNodes("//*[contains(name(),'result')]")
        Debug.Print "Document ID: "; xmlnode.text
    Next

Please help

CDP1802 · Accepted Answer · 2021-05-07 13:17:15Z

1

You need to clean-up the response.

    Dim xmldoc As Object
    Dim xmlnode As Object
    Set xmldoc = CreateObject("Microsoft.XMLDOM")
    xmldoc.SetProperty "SelectionLanguage", "XPath"
    xmldoc.async = False
    
    Dim xml As String
    xml = .ResponseText
    xml = Mid(xml, InStr(1, xml, "<?xml "))
    xml = Left(xml, InStr(1, xml, "Envelope>") + Len("Envelope"))

    If xmldoc.LoadXML(xml) = True Then
        For Each xmlnode In xmldoc.SelectNodes("//*[contains(name(),'result')]")
            Debug.Print "Document ID: "; xmlnode.Text
        Next
    Else
        MsgBox "XML failed to load", vbCritical
    End If

answered May 7, 2021 at 13:17

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parse SOAP XML Response in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related