'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
– Axel Richter, Commented Dec 17, 2015 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 — user1641172
– user1641172, Commented Dec 17, 2015 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
– Axel Richter, Commented Dec 17, 2015 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! — user1641172
– user1641172, Commented Dec 17, 2015 at 16:44

Parfait · Accepted Answer · 2015-12-18 04:29:31Z

1

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

answered Dec 18, 2015 at 4:29

Parfait

108k19 gold badges103 silver badges138 bronze badges

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

1 Comment

stokebob Over a year ago

Works perfectly! Thanks for detailed explanation, this has explained a lot from me.

Collectives™ on Stack Overflow

'SelectSingleNode' XPath query from XML with multiple Namespaces

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related