How to Parse XML Using DOMDocument in VBA

June 6, 2023

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

>Solution :

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