VBA XML parsing - looping through child nodes

Question

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

<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 values is a child node of its parent Variable

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

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

Possible duplicate of [How to parse XML using vba](http://stackoverflow.com/questions/11305/how-to-parse-xml-using-vba) — bzimor, Nov 17 '16 at 15:48

Cody Ｇ · Accepted Answer · 2017-09-15T15:16:10.627

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.

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!

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.

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).

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

If I run your code "as is", I get the "User-defined type not defined" error when declaring the `objXML` variable — horace_vr, Nov 17 '16 at 16:23
no, did not see that edit :). But if I add it and share the code with someone else, will they need to add it too by themselves ? This may not be an option, unfortunately — horace_vr, Nov 17 '16 at 16:48
I avoided the need to add the reference by declaring all variables `as Object`, and setting `Set objXML = CreateObject("Microsoft.XMLDOM")` — horace_vr, Nov 17 '16 at 16:52

score 4 · Answer 2 · 2016-11-17T18:18:04.447

The key to working with HTML or XML elements is using the Locals and Watch windows to browser the element's' properties. Sub items will be in childNode, children or item collections. 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.

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

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 childNodes collection using the Immediate Window

While this does not solve the specific question, it outlines an excellent procedure to be used by amateur coders when dealing with situations like the on in the question — horace_vr, Nov 17 '16 at 17:15
@horace_vr for better or worse that was my intention. I should have continued on to create the loop, but Cody G. already provided you with the correct answer. — , Nov 17 '16 at 17:32
@horace_vr I went ahead and updated my answer with a `For Loop` and a `For each Loop ` demo. — , Nov 17 '16 at 18:19

VBA XML parsing - looping through child nodes

2 Answers2

Linked