VBA Excel SelectSingleNode syntax

Question

I'm using the following code to get the value of the "DistanceUnit" element in my xml:

Dim xmlDoc As MSXML2.DOMDocument60 Dim xmlElement As MSXML2.IXMLDOMElement Set xmlDoc = New MSXML2.DOMDocument60 xmlDoc.async = False xmlDoc.validateOnParse = False xmlDoc.LoadXML strResponse Set xmlElement = xmlDoc.DocumentElement Set curNode = xmlElement.SelectSingleNode("/Response/ResourceSets/ResourceSet/Resources/Route/DistanceUnit")

When debugging I see that curNode is NOTHING. I don't understand why. When I use an itterative code it seems to work ok:

Set xmlRoot = xmlDoc.DocumentElement Set xmlChildren = xmlRoot.ChildNodes For Each xmlTemplate In xmlChildren If xmlTemplate.nodeName = "ResourceSets" Then MsgBox "found!" Exit For End If Next xmlTemplate

I don't want to use itterative code, since I know the exact xpath to the element...

This code work as well, but I just want to use the xpath:

Set curNode = xmlRoot.ChildNodes(6).ChildNodes(0).ChildNodes(1).ChildNodes(0).ChildNodes(2) MsgBox curNode.Text

Thanks, Li

My xml:

<Response xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/search/local/ws/rest/v1"> .... <StatusCode>200</StatusCode> <StatusDescription>OK</StatusDescription> <AuthenticationResultCode>ValidCredentials</AuthenticationResultCode> <ResourceSets> <ResourceSet> <EstimatedTotal>1</EstimatedTotal> <Resources> <Route> ..... <DistanceUnit>Kilometer</DistanceUnit> ..... </Route> </Resources> </ResourceSet> </ResourceSets> </Response>

Since no namespace prefix(neither 'xsi:' nor 'xsd:') is used in your XML, you can remove (if you can control the XML source) the default namespace prefix declaration( xmlns="schemas.microsoft.com/search/local/ws/rest/v1" ) in the first line of the XML source. By removing the default namespace prefix, you don't have use any prefix before the xml node names. Or just change "<Response xmlns:~~~>" to the simple "<Response>". This is a workaround, not a solution. Or you have to use the marked answer's method. — konahn
– konahn, Commented Feb 18, 2020 at 12:16

to StackOverflow · Accepted Answer · 2013-11-18 07:43:21Z

Try specifying a namespace prefix for your default namespace declaration (see this KB article for more info):

Set xmlDoc = New MSXML2.DOMDocument60 xmlDoc.async = False xmlDoc.validateOnParse = False xmlDoc.setProperty "SelectionNamespaces", "xmlns:a='http://schemas.microsoft.com/search/local/ws/rest/v1'" xmlDoc.LoadXML strResponse Set xmlElement = xmlDoc.DocumentElement Set curNode = xmlElement.SelectSingleNode("/a:Response/a:ResourceSets/a:ResourceSet/a:Resources/a:Route/a:DistanceUnit")

Collectives™ on Stack Overflow

VBA Excel SelectSingleNode syntax

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related