Excel VBA从XML获取特定节点 [英] Excel VBA getting specific node from XML
问题描述
例如，如果eventNode是 我已经看了很多通过论坛和 示例XML： 我想输出每个 p>尝试这样 - 你可以修改下面的代码来获取任何子节点。 这很好。对于任何读者来说，这是我如何使用上面的答案来调整我的代码（因为我从URL加载XML而不是文件）： 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 I have looked a lot through forums and the Sample XML: I want to output the text in each Try this - you can modify the below code to fetch any child node
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):
这篇关于Excel VBA从XML获取特定节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！< event>< ID> 1< / ID> ...< title>事件1<标题>< / event> ，如何获取 1 从只知道节点名称是 ID
.SelectSingleNode 给了我没有运气。另外 .selectNodes 不会像XML字符串中的正常节点列表一样。我不知道这是否是由于我用来解析我的XML文件的方法。
Sub ListEvents（）
Dim strPath As String
strPath = getAPI（GetEvents，filter =& orderBy =）
Dim xmlDocument As MSXML2。 DOMDocument60
设置xmlDocument =新建DOMDocument60
使用CreateObject（MSXML2.XMLHTTP）
。打开GET，strPath，False
.send
xmlDocument.LoadXML .responseText
结束
Dim lvl1 As IXMLDOMNode：Dim lvl2 As IXMLDOMNode
Dim eventNode As IXMLDOMNode：Dim isNode As IXMLDOMNode
For每个lvl1在xmlDocument.ChildNodes
对于每个lvl2在lvl1.ChildNodes
对于每个eventNode在lvl2.ChildNodes
如果eventNode.HasChildNodes然后
'这里是我想要的代码找到特定子节点
'，而无需查看每个节点。
结束如果
下一个
下一个
下一个
End Sub
<？xml version =1.0encoding =utf -8？>
< ResultsOfListOfEvent xmlns：xsi =http://www.w3.org/2001/XMLSchema-instancexmlns：xsd =http://www.w3.org/2001/XMLSchemaxmlns = http://www.regonline.com/api\">
< Success> true< / Success>
<数据>
< APIEvent>
< ID> 111< / ID>
< CustomerID> 222< / CustomerID>
< ParentID> 0< / ParentID>
<状态>测试< / Status>
< Title>事件名称< / Title>
< ClientEventID />
< TypeID> 9< / TypeID>
< TimeZone> GMT< / TimeZone>
< CurrencyCode> GBP< / CurrencyCode>
< AddDate> 2013-12-18T02：34：09.357< / AddDate>
< Channel>运行< / Channel>
< IsWaitlisted> false< / IsWaitlisted>
< / APIEvent>
< APIEvent>
< ID> 112< / ID>
< CustomerID> 223< / CustomerID>
< ParentID> 0< / ParentID>
<状态>测试< / Status>
< Title>事件名称< / Title>
< ClientEventID />
< TypeID> 9< / TypeID>
< TimeZone> GMT< / TimeZone>
< CurrencyCode> GBP< / CurrencyCode>
< AddDate> 2013-12-18T02：34：09.357< / AddDate>
< Channel>运行< / Channel>
< IsWaitlisted> false< / IsWaitlisted>
< / APIEvent>
< / Data>
< / ResultsOfListOfEvent>
< ID> （即 111 和 112 ）和每个< Title& code>。这只是一个例子，根据我运行的API，我想要能够选择我选择的信息。
设置xmlDoc = CreateObject（Microsoft.XMLDOM ）
xmlDoc.SetPropertySelectionLanguage，XPath
xmlDoc.Async = False
xmlDoc.Load（C：\Users\pankaj.jaju\Desktop\Test。 xml）
设置nodeXML = xmlDoc.getElementsByTagName（ID）
对于i = 0到nodeXML.Length - 1
MsgBox nodeXML（i）.Text
下一个
从问题作者编辑：
Sub ListEvents（）
Dim myURL As String
myURL = getAPI（GetEvents，filter =& orderBy =）
设置xmlDoc = CreateObject（Microsoft.XMLDOM）
xmlDoc.setPropertySelectionLanguage，XPath
xmlDoc.async = False
使用CreateObject（MSXML2.XMLHTTP）
。打开GET，myURL，False
.send
xmlDoc.LoadXML .responseText
结束
设置nodeXML = xmlDoc.getElementsByTagName（ ID）
对于i = 0到nodeXML.Length - 1
MsgBox nodeXML（i）.Text
下一个
End Sub
<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)?.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
<?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>
<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.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
Edit from question author:
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
