从XML获取特定节点的Excel VBA

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 1 from just knowing the node name is ID (or any other value I want to pull out)?

例如，如果eventNode是 1 … ，如何只知道节点名为ID(或我想提取的其他值)就得到1 ?

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.

我在论坛上看了很多，但是。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. 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.

我想在每个 (即111和112)和每个

2 个解决方案

#1

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

#2

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
• 使用xmlms2命名空间作为旧的Microsoft。XMLDOM 1只用于遗留支持——请参见这里
• 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)
• 使用“SelectionNamespaces”属性解决文档具有默认名称空间时MSXML2与XPath的问题——参见这里。我们创建了一个名为r(任何名称都可以)的名称空间，该名称空间具有与文档中的默认名称空间相同的URI引用(在本例中为http://www.regonline.com/api)
• 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
• 使用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