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

bzimor
– bzimor

2016-11-17 15:48:13 +00:00
Commented Nov 17, 2016 at 15:48 — bzimor
– bzimor, Commented Nov 17, 2016 at 15:48

Cody Geisler · Accepted Answer · 2017-09-15 15:16:10Z

4

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

edited Sep 15, 2017 at 15:16

answered Nov 17, 2016 at 16:01

Cody Geisler

8,6272 gold badges41 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

horace_vr Over a year ago

If I run your code "as is", I get the "User-defined type not defined" error when declaring the objXML variable

Cody Geisler Over a year ago

Did you add the xml reference? O_o

horace_vr Over a year ago

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 Over a year ago

I avoided the need to add the reference by declaring all variables as Object, and setting Set objXML = CreateObject("Microsoft.XMLDOM")

score 4 · Accepted Answer · 2016-11-17 18:18:04Z

4

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

edited Nov 17, 2016 at 18:18

answered Nov 17, 2016 at 16:52

user6432984

3 Comments

horace_vr Over a year ago

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

user6432984 Over a year ago

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

user6432984 Over a year ago

@horace_vr I went ahead and updated my answer with a For Loop and a For each Loop demo.

Collectives™ on Stack Overflow

VBA XML parsing - looping through child nodes

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related