'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 `ns1`and not `ns2`. — Axel Richter, Dec 17 '15 at 13:50
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` — , Dec 17 '15 at 14:43
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, Dec 17 '15 at 15:40
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! — , Dec 17 '15 at 16:44

score 1 · Accepted Answer · answered Dec 18 '15 at 04:29

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

Works perfectly! Thanks for detailed explanation, this has explained a lot from me. — stokebob, Dec 18 '15 at 14:13

'SelectSingleNode' XPath query from XML with multiple Namespaces

1 Answers1

Linked