How to Parse XML Using DOMDocument in VBA

Question

How would I get the value of the node indicated below in this XML document.

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
    <s:Header> 
        <h:ResponseContext xmlns:h="http://purolator.com/pws/datatypes/v2" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <h:ResponseReference>UserRef</h:ResponseReference>
        </h:ResponseContext>
    </s:Header>
    <s:Body>
        <CreateShipmentResponse xmlns="http://purolator.com/pws/datatypes/v2" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <ResponseInformation>
                <Errors/>
                <InformationalMessages i:nil="true"/>
            </ResponseInformation>
            <ShipmentPIN>
                <Value>329035959744</Value> ' <-- This is the node I want the value of
            </ShipmentPIN>
            <PiecePINs>
                <PIN>
                    <Value>329035959744</Value>
                </PIN>
                <PIN>
                    <Value>329035959751</Value>
                </PIN>
            </PiecePINs>
        </CreateShipmentResponse>
    </s:Body>
</s:Envelope>

I have tried using information from this other question that was answered but it does not return anything when I run the code. (Excel VBA getting specific node from XML)

Set response = CreateObject("MSXML2.DOMDocument")
response.SetProperty "SelectionLanguage", "XPath"
response.Async = False
response.validateOnParse = False
response.Load(respPath)

Set nodeXML = xmlDoc.getElementsByTagName("Value")
For i = 0 To nodeXML.Length - 1
    Debug.Print nodeXML(i).Text
Next

"does not seem to be working" - exactly how does it not work? It gives an error? What error? — Tim Williams
– Tim Williams, Commented Jun 6, 2023 at 18:03
Your XML doc is response but you're calling xmlDoc.getElementsByTagName. Add Option Explicit at the top of the module and fix any problems that uncovers. — Tim Williams
– Tim Williams, Commented Jun 6, 2023 at 18:14

zx485 · Accepted Answer · 2023-06-06 18:14:29Z

2

You forgot to take the namespace into account. So add the line

response.setProperty "SelectionNamespaces", "xmlns:pur='http://purolator.com/pws/datatypes/v2'"

and change your code to

Set response = CreateObject("MSXML2.DOMDocument")
response.setProperty "SelectionLanguage", "XPath"
response.setProperty "SelectionNamespaces", "xmlns:pur='http://purolator.com/pws/datatypes/v2'"
response.Async = False
response.validateOnParse = False
response.Load(respPath)

Set nodeXML = response.selectNodes("//pur:ShipmentPIN/pur:Value")
For i = 0 To nodeXML.Length - 1
    Debug.Print nodeXML(i).Text
Next

answered Jun 6, 2023 at 18:14

zx485

29.1k28 gold badges55 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

How to Parse XML Using DOMDocument in VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related