I have an XML file from a URL API (the URL I have not shared since it allows access to secure info). From this file I want to get certain info out. My problem is, once I am in the parent node (eventNode) I want to simply be able to get the data from specific child nodes.
For example if eventNode was <event><ID>1</ID>...<title>event 1</title></event>
, how would I get 1
from just knowing the node name is ID
(or any other value I want to pull out)?
I have looked a lot through forums and the .SelectSingleNode
has given me no luck. Also .selectNodes
will not act like a normal list of nodes in an XML string. I don't know if this is due to the method I'm using 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
Sample XML:
<?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 output the text in each <ID>
(i.e. 111
and 112
) and each <Title>
. This is only an example, depending on the API I run I will want to be able to pick and choose what information I pull.
Try this - you can modify the below code to fetch 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
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 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
Here's a slightly different approach that builds on Pankaj Jaju's answer above.
Notes:
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)/ID
, we'll look 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With