VBA XML 解析 - 通过子节点循环

VBA XML parsing - looping through child nodes

提问by horace_vr

This is my first attempt at parsing XML files using VBA, so I may be missing the obvious; I can already print this:

这是我第一次尝试使用 VBA 解析 XML 文件，所以我可能会遗漏明显的；我已经可以打印这个：

<values>
    <value code="1">A</value>
    <value code="2">B</value>
    <value code="3">C</value>
</values>

using this code line:

使用此代码行：

Debug.Print Variable.SelectSingleNode("values").XML

, where valuesis a child node of its parent Variable

, 哪里values是其父节点的子节点Variable

But I can't figure out is how to loop through the values's children, and print the "1A", "2B", "3C" pairs

但我不知道如何循环遍历values的孩子，并打印“1A”、“2B”、“3C”对

As far as I can understand, this questionuses the first child of the root, while my goal is to get deeper into a multiple-leveled structure.

据我所知， 这个问题使用了根的第一个孩子，而我的目标是深入到一个多层次的结构中。

回答by Cody Ｇ

Here we can see how to use the MSXML6.0 Library to Parse XML for your particular example. To use this example, you need to add a reference to MSXML6.0 in your VBA project.

在这里，我们可以看到如何使用 MSXML6.0 库为您的特定示例解析 XML。要使用此示例，您需要在 VBA 项目中添加对 MSXML6.0 的引用。

I suggest you pay particular attention to the XPath variable '//value' and selectors such as .getNamedItem("code") --- there are many more of these that you would need to familiarize yourself with in order to become fluent in XML parsing. Fortunately a lot of this passes over into HTML parsing so it is a useful skill!

我建议您特别注意 XPath 变量 '//value' 和选择器，例如 .getNamedItem("code") --- 为了熟练使用 XML，您需要熟悉更多这些解析。幸运的是，很多这些都传递到 HTML 解析中，因此这是一项有用的技能！

In this case, I have selected ALL value nodes. Iterating through them is as simple as doing a for loop based on the length of the array of nodes and using the .item(i) call.

在这种情况下，我选择了所有值节点。遍历它们就像根据节点数组的长度执行 for 循环并使用 .item(i) 调用一样简单。

Option Explicit
Sub test()
    Dim strXml As String
    strXml = "<values><value code=""1"">A</value><value code=""2"">B</value><value code=""3"">C</value></values>"

    Dim objXML As MSXML2.DOMDocument60
    Set objXML = New MSXML2.DOMDocument60

    If Not objXML.LoadXML(strXml) Then  'strXML is the string with XML'
        Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason
    End If

    Dim entry_point As IXMLDOMNode
    Set entry_point = objXML

    Dim myNodes As IXMLDOMNodeList
    Dim myElement As IXMLDOMElement
    Dim myNode As IXMLDOMNode
    Dim nNode As Integer
    Set myNodes = entry_point.SelectNodes("//value")
    If myNodes.Length > 0 Then
        For nNode = 0 To myNodes.Length
            Set myNode = myNodes(nNode) ' Get the first node.
            If myNode Is Nothing Then
            Else
                Debug.Print myNode.Text
                Debug.Print myNode.Attributes.getNamedItem("code").Text
            End If
        Next nNode
    Else
        Debug.Print "No nodes found."
    End If

End Sub

Here is another case where I select all VALUES nodes and then iterate through the children of each VALUES node (assuming that all values nodes only have value children).

这是另一种情况，我选择所有 VALUES 节点，然后遍历每个 VALUES 节点的子节点（假设所有值节点只有值子节点）。

Option Explicit
Sub test()
    Dim strXml As String
    strXml = "<values><value code=""1"">A</value><value code=""2"">B</value><value code=""3"">C</value></values>"

    Dim objXML As MSXML2.DOMDocument60
    Set objXML = New MSXML2.DOMDocument60

    If Not objXML.LoadXML(strXml) Then  'strXML is the string with XML'
        Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason
    End If

    Dim entry_point As IXMLDOMNode
    Set entry_point = objXML

    Dim myNodes As IXMLDOMNodeList
    Dim myChildNodes As IXMLDOMNodeList
    Dim myElement As IXMLDOMElement
    Dim myNode As IXMLDOMNode
    Dim myChildNode As IXMLDOMNode
    Dim nNode As Integer
    Dim nChildNode As Integer
    Set myNodes = entry_point.SelectNodes("//values")
    If myNodes.Length > 0 Then
        For nNode = 0 To myNodes.Length - 1
            Set myNode = myNodes(nNode)
            If myNode Is Nothing Then
            Else
                Set myChildNodes = myNode.ChildNodes ' Get the children of the first node.
                For nChildNode = 0 To myChildNodes.Length - 1
                    Debug.Print myChildNodes(nChildNode).Text
                    Debug.Print myChildNodes(nChildNode).Attributes.getNamedItem("code").Text
                Next nChildNode
            End If
        Next nNode
    Else
        Debug.Print "No nodes found."
    End If

End Sub

回答by Cody Ｇ

The key to working with HTML or XML elements is using the Localsand Watchwindows to browser the element's' properties. Sub items will be in childNode, childrenor itemcollections. I will also open the document in a Chrome, click my target element, and use Copy XPath when applicable. I can them use the XPath to track down my target element.

使用 HTML 或 XML 元素的关键是使用Locals和Watch窗口来浏览元素的属性。分项目将在childNode，children或者item集合。我还将在 Chrome 中打开文档，单击我的目标元素，并在适用时使用 Copy XPath。我可以让他们使用 XPath 来追踪我的目标元素。

In this example I place Stopafter the line in which my target node is set. Next I drill down through the properties of the node (testing in the Immediate Windowas I go) till I find the property that I am looking for.

在此示例中，我将放置Stop在设置目标节点的行之后。接下来，我深入了解节点的属性（在Immediate Window我进行时进行测试），直到找到我正在寻找的属性。

Sub TestStub()
    Const XMLTEST = "<values>" & _
          "<value code=""1"">A</value>" & _
          "<value code=""2"">B</value>" & _
          "<value code=""3"">C</value>" & _
          "</values>"
    Dim objXML As Object, node As Object

    Set objXML = CreateObject("MSXML2.DOMDocument")

    If Not objXML.LoadXML(XMLTEST) Then  'strXML is the string with XML'
        Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason

    Else
        Set node = objXML.SelectSingleNode("values")
        Stop

    End If
End Sub

Update: how to test looping through the items in the childNodescollection using the Immediate Window

更新：如何childNodes使用Immediate Window