Excel VBA 从 XML 获取特定节点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21047044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA getting specific node from XML
提问by yoz1234
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.
我有一个来自 URL API 的 XML 文件（我没有共享该 URL，因为它允许访问安全信息）。我想从这个文件中获取某些信息。我的问题是，一旦我在父节点 (eventNode) 中，我只想能够从特定的子节点获取数据。
For example if eventNode was <event><ID>1</ID>...<title>event 1</title></event>, how would I get 1from just knowing the node name is ID(or any other value I want to pull out)?
例如，如果 eventNode 是<event><ID>1</ID>...<title>event 1</title></event>，我将如何1仅知道节点名称是ID（或我想提取的任何其他值）？
I have looked a lot through forums and the .SelectSingleNodehas given me no luck. Also .selectNodeswill 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.
我通过论坛看了很多，但.SelectSingleNode没有给我运气。也.selectNodes不会像 XML 字符串中的普通节点列表那样工作。我不知道这是否是由于我用来解析 XML 文件的方法。
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：
<?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. 111and 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.
我想在每个<ID>（即111和112）和每个<Title>. 这只是一个示例，根据我运行的 API，我希望能够挑选和选择我提取的信息。
采纳答案by Pankaj Jaju
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
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):
这很好用。对于任何读者，这就是我如何使用上面的答案来调整我的代码（因为我从 URL 加载 XML - 而不是文件）：
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
回答by barrowc
Here's a slightly different approach that builds on Pankaj Jaju's answer above.
这是一种稍微不同的方法，它建立在上面 Pankaj Jaju 的回答之上。
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/apiin 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
- 使用 MSXML2 命名空间作为旧的 Microsoft.XMLDOM 仅维护旧版支持 - 请参阅此处
- 当文档具有默认命名空间时，使用“SelectionNamespaces”属性来修复 MSXML2 与 XPath 的问题 - 请参见此处。我们创建了一个名为
r（任何名称都可以）的命名空间，它与文档中的默认命名空间具有相同的 URI 引用（http://www.regonline.com/api在本例中） - 使用 XPath 中的新命名空间作为任何具有无前缀名称的元素的前缀。这是一种复杂的说法，而不是寻找
/ID，我们将寻找/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
