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