Parse XML SOAP document using VBA

Question

I will be grateful for any help. I need to parse XML SOAP document but always I get the same result -> plain text without tags. How Can I get particular/single tag (f.e. vatNumber) value from XML ? Thank you.

This is my VBA code:

ObjHTTP.Open "Post", sURL, False
ObjHTTP.setRequestHeader "Content-Type", "text/xml"
ObjHTTP.send (sEnv)

'parse xml response - output
Dim responseDocument As MSXML2.DOMDocument60
Set responseDocument = New MSXML2.DOMDocument60

responseDocument.async = False
responseDocument.validateOnParse = False
responseDocument.SetProperty "SelectionNamespaces", " xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"    
responseDocument.LoadXML (ObjHTTP.responseText)

If responseDocument.parseError.reason <> "" Then
      MsgBox m_objDOMPeople.parseError.reason
     Exit Sub
End If

MsgBox responseDocument.SelectNodes("/soap:Envelope/soap:Body")(0).Text
Set ObjHTTP = Nothing
Set xmldoc = Nothing

This is XML SOAP input:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
       <soap:Body>
          <checkVatResponse xmlns="urn:ec.europa.eu:taxud:vies:services:checkVat:types">
             <countryCode>SK</countryCode>
             <vatNumber>204566287588</vatNumber>
             <requestDate>2015-04-07+01:00</requestDate>
             <valid>true</valid>
             <name>Company k. s.</name>
             <address>Some Address</address>
          </checkVatResponse>
       </soap:Body>
    </soap:Envelope>

QHarr · Accepted Answer · 2019-03-07 11:24:56Z

2

Either remove the offending namespace from the xpath search

Set node = xmlDoc.SelectSingleNode("//*[local-name()='vatNumber']")
Debug.Print node.Text

Or remove that second xmlns from the response then parse (you could regex replace for example)

Dim node As IXMLDOMElement
Set node = xmlDoc.SelectSingleNode("//vatNumber")
Debug.Print node.Text

answered Mar 7, 2019 at 11:24

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

1 Comment

Tomáš Jalč Over a year ago

Yes, thank you. First option works great ! I removed namespace and xmlns (whole row) and it works.

Collectives™ on Stack Overflow

Parse XML SOAP document using VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related