Excel VBA getting specific node from XML

Here's a slightly different approach that builds on Pankaj Jaju's answer above.

Notes:

• uses the MSXML2 namespace as the old Microsoft.XMLDOM one is only maintained for legacy support - see here
• uses the "SelectionNamespaces" property to fix the problem which MSXML2 has with XPath when a document has a default namespace - see here. We create a namespace called r (any name would do though) that has the same URI reference as the default namesepace in the document (http://www.regonline.com/api in this case)
• uses that new namespace in XPath as the prefix for any element which has an unprefixed name. That's a complicated way of saying that rather than looking for /ID, we'll look for /r:ID

Here is the code:

Sub foo()Dim xmlDoc As ObjectDim xmlNodeList As ObjectDim xmlNode As ObjectSet xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")xmlDoc.setProperty "SelectionNamespaces", "xmlns:r='http://www.regonline.com/api'"xmlDoc.async = FalsexmlDoc.Load "C:\Users\colin\Desktop\yoz1234.xml"Set xmlNodeList = xmlDoc.selectNodes("/r:ResultsOfListOfEvent/r:Data/r:APIEvent/r:ID")For Each xmlNode In xmlNodeList    MsgBox xmlNode.TextNext xmlNodeEnd Sub

Try this - you can modify the below code to fetch any child node

Set xmlDoc = CreateObject("Microsoft.XMLDOM")xmlDoc.SetProperty "SelectionLanguage", "XPath"xmlDoc.Async = FalsexmlDoc.Load("C:\Users\pankaj.jaju\Desktop\Test.xml")Set nodeXML = xmlDoc.getElementsByTagName("ID")For i = 0 To nodeXML.Length - 1    MsgBox nodeXML(i).TextNext

Edit from question author:

This worked great. For any readers this is how I used the answer above to adapt my code (since I load the XML from a URL - not a file):

Sub ListEvents()Dim myURL As StringmyURL = getAPI("GetEvents", "filter=&orderBy=")Set xmlDoc = CreateObject("Microsoft.XMLDOM")xmlDoc.setProperty "SelectionLanguage", "XPath"xmlDoc.async = FalseWith CreateObject("MSXML2.XMLHTTP")    .Open "GET", myURL, False    .send    xmlDoc.LoadXML .responseTextEnd WithSet nodeXML = xmlDoc.getElementsByTagName("ID")For i = 0 To nodeXML.Length - 1    MsgBox nodeXML(i).TextNextEnd Sub