Excel VBA gets concrete node from XML

Question

I have an XML file from an API URL (a URL that I have not used since it provides access to protected information). From this file I want to get certain information. My problem is that when I am in the parent node (eventNode), I just want to get data from certain child nodes.

For example, if eventNode was <event><ID>1</ID>...<title>event 1</title></event> , how would I get 1 only from knowing the name of node ID (or any other value, which I want to pull out)?

I looked through the forums a lot, and .SelectSingleNode did not give me luck. Also .selectNodes will not behave like a normal list of nodes in an XML string. I do not know if this is related to the method that I use to parse my XML file.

 Sub ListEvents() Dim strPath As String strPath = getAPI("GetEvents", "filter=&orderBy=") Dim xmlDocument As MSXML2.DOMDocument60 Set xmlDocument = New DOMDocument60 With CreateObject("MSXML2.XMLHTTP") .Open "GET", strPath, False .send xmlDocument.LoadXML .responseText End With Dim lvl1 As IXMLDOMNode: Dim lvl2 As IXMLDOMNode Dim eventNode As IXMLDOMNode: Dim isNode As IXMLDOMNode For Each lvl1 In xmlDocument.ChildNodes For Each lvl2 In lvl1.ChildNodes For Each eventNode In lvl2.ChildNodes If eventNode.HasChildNodes Then 'Here is where I want code to find specific child node 'without having to look at every node. End If Next Next Next End Sub 

XML example:

 <?xml version="1.0" encoding="utf-8" ?> <ResultsOfListOfEvent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.regonline.com/api"> <Success>true</Success> <Data> <APIEvent> <ID>111</ID> <CustomerID>222</CustomerID> <ParentID>0</ParentID> <Status>Testing</Status> <Title>Event Name</Title> <ClientEventID /> <TypeID>9</TypeID> <TimeZone>GMT</TimeZone> <CurrencyCode>GBP</CurrencyCode> <AddDate>2013-12-18T02:34:09.357</AddDate> <Channel>Running</Channel> <IsWaitlisted>false</IsWaitlisted> </APIEvent> <APIEvent> <ID>112</ID> <CustomerID>223</CustomerID> <ParentID>0</ParentID> <Status>Testing</Status> <Title>Event Name</Title> <ClientEventID /> <TypeID>9</TypeID> <TimeZone>GMT</TimeZone> <CurrencyCode>GBP</CurrencyCode> <AddDate>2013-12-18T02:34:09.357</AddDate> <Channel>Running</Channel> <IsWaitlisted>false</IsWaitlisted> </APIEvent> </Data> </ResultsOfListOfEvent> 

I want to display text in each <ID> (i.e. 111 and 112 ) and each <Title> . This is just an example, depending on the launch of the API. I want to be able to choose what information I pull out.

Answer 1

Try this - you can change the code below to select any child node

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

---

Change question author:

It did a great job. For all readers, this is how I used the answer above to adapt my code (since I'm loading XML from a URL, not a file):

 Sub ListEvents() Dim myURL As String myURL = getAPI("GetEvents", "filter=&orderBy=") Set xmlDoc = CreateObject("Microsoft.XMLDOM") xmlDoc.setProperty "SelectionLanguage", "XPath" xmlDoc.async = False With CreateObject("MSXML2.XMLHTTP") .Open "GET", myURL, False .send xmlDoc.LoadXML .responseText End With Set nodeXML = xmlDoc.getElementsByTagName("ID") For i = 0 To nodeXML.Length - 1 MsgBox nodeXML(i).Text Next End Sub 

Answer 2

Here's a slightly different approach, which builds on Pankaj Jaju above.

Notes:

• uses the MSXML2 namespace because the old version of Microsoft.XMLDOM is only supported to support the legacy version - see here
• uses the "SelectionNamespaces" property to fix the problem that MSXML2 has XPath when the document has a default namespace - see here . We create a namespace with the name r (any name should do) that has the same URI reference as the default object name in the document ( http://www.regonline.com/api in this case)
• uses this new namespace in XPath as a prefix for any element with an unsigned name. This is a tricky way of saying that instead of searching for /ID we will search for /r:ID

Here is the code:

 Sub foo() Dim xmlDoc As Object Dim xmlNodeList As Object Dim xmlNode As Object Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0") xmlDoc.setProperty "SelectionNamespaces", "xmlns:r='http://www.regonline.com/api'" xmlDoc.async = False xmlDoc.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.Text Next xmlNode End Sub