'SelectSingleNode' XPath query from XML with multiple Namespaces

Question

Here's part of an example XML I am trying to parse:

<GetCompetitivePricingForASINResponse xmlns="http://mws.amazonservices.com/schema/Products/2011-10-01">
    <GetCompetitivePricingForASINResult ASIN="B014P3CM08" status="Success">
        <Product xmlns="http://mws.amazonservices.com/schema/Products/2011-10-01" xmlns:ns2="http://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd">
            <Identifiers>
                <MarketplaceASIN>
                    <MarketplaceId>A1F83G8C2ARO7P</MarketplaceId>
                    <ASIN>B014P3CM08</ASIN>
                </MarketplaceASIN>
            </Identifiers>
            <CompetitivePricing>
                <CompetitivePrices>
                    <CompetitivePrice belongsToRequester="false" condition="New" subcondition="New">
                        <CompetitivePriceId>1</CompetitivePriceId>
                            <Price>
                                <LandedPrice>
                                    <CurrencyCode>GBP</CurrencyCode>
                                    <Amount>24.00</Amount>
                                </LandedPrice>
                                <ListingPrice>
                                    <CurrencyCode>GBP</CurrencyCode>
                                    <Amount>24.00</Amount>
                                </ListingPrice>
                                <Shipping>
                                    <CurrencyCode>GBP</CurrencyCode>
                                    <Amount>0.00</Amount>
                                </Shipping>
                            </Price>
                        </CompetitivePrice>
                    </CompetitivePrices>
.... etc

I am trying to target and retrieve text from the /Price/LandedPrice/Amount/ node (24.00 in this case). Here's the VBA code I have so far:

Dim strXMLSite As String
Dim objXMLHTTP As MSXML2.XMLHTTP60
Dim objXMLDoc As MSXML2.DOMDocument60
Dim objXMLNode1 As MSXML2.IXMLDOMNode

Set objXMLHTTP = New MSXML2.XMLHTTP60
Set objXMLDoc = New MSXML2.DOMDocument60

Call MWSProductAPI

strXMLSite = Worksheets("Settings").Range("B12").Value

objXMLHTTP.Open "GET", strXMLSite, False
objXMLHTTP.send
objXMLDoc.LoadXML (objXMLHTTP.responseText)

XmlNamespaces = "xmlns:ns2='http://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd' xmlns:ns1='http://mws.amazonservices.com/schema/Products/2011-10-01'"

objXMLDoc.setProperty "SelectionNamespaces", XmlNamespaces

Set objXMLNode1 = objXMLDoc.SelectSingleNode("/ns2:GetCompetitivePricingForASINResponse/ns2:GetCompetitivePricingForASINResult/ns2:Product/ns2:CompetitivePricing/ns2:CompetitivePrices/ns2:CompetitivePrice/ns2:Price/ns2:LandedPrice/ns2:Amount")
Worksheets("Settings").Range("C8").Value = objXMLNode1.text

However, when I run the code in Excel it returns the error:

'Object variable or With block reference not set'.

Debugging the code shows that the SelectSingleNode is returning nothing. My understanding of the namespace prefix and how this fits into the XPath is very limited. I can find plenty of examples for XML with namespaces, however, examples where there are multiple namespaces are very limited.

The elements you try to get are in the namespace xmlns="http://mws.amazonservices.com/schema/Products/2011-10-01". From your property "SelectionNamespaces" this is ns1and not ns2. — Axel Richter
Yeah like axel says you are missing your first namespace, you can skip the elements above product by starting with a // e.g. //ns2:Product — user1641172
Also the element Product is not in the namespace ns2. In the posted XML code none of the elements are in this namespace. — Axel Richter
Ah yes well spotted, xmlns:ns2 should probably be xsi:schemaLocation, don't know why you would be using the name of a schema as a namespace! — user1641172

Parfait Parfait · Accepted Answer · 2015-12-18T04:29:31

Because you have a default namespace in root tag, you only need to declare one namespace, not multiple. Consider the following adjustment in removing the other declaration:

XmlNamespaces = "xmlns:ns2='http://mws.amazonservices.com/schema/Products/2011-10-01'"

objXMLDoc.setProperty "SelectionNamespaces", XmlNamespaces

Set objXMLNode1 = objXMLDoc.SelectSingleNode("/ns2:GetCompetitivePricingForASINResponse" _
                            & "/ns2:GetCompetitivePricingForASINResult/ns2:Product" _
                            & "/ns2:CompetitivePricing/ns2:CompetitivePrices" _
                            & "/ns2:CompetitivePrice/ns2:Price/ns2:LandedPrice/ns2:Amount")

By the way you can shorten the XPath expression:

Set objXMLNode1 = objXMLDoc.SelectSingleNode("//ns2:Price/ns2:LandedPrice/ns2:Amount")

Even use a list should more than one LandedPrice appear (but change declaration as node list and use index notation for return value):

...
Dim objXMLNode1 As MSXML2.IXMLDOMNodeList
...

Set objXMLNode1 = objXMLDoc.DocumentElement.SelectNodes(" _
                       & //ns2:Price/ns2:LandedPrice/ns2:Amount")

Worksheets("Settings").Range("C8").Value = objXMLNode1(0).Text

'SelectSingleNode' XPath query from XML with multiple Namespaces

1 Answers