VBA – Select a Single Node XML

Posted on February 19, 2018 by

Selecting and working with XML in Excel and VBA is always a bit challenging. Whenever a programmer (or a developer or an analyst or a portfolio controller) hears about XML and VBA, something weird happens in their minds. The programmer thinks that he should go back to Java (or C, C#, Python, etc.), the analyst thinks how to pass the task to the programming department and the portfolio controller think about going and asking of a better input. Even a *.csv file would be probably better.

Anyhow, lets imagine that our input XML data looks like this (the whole data is here):

<FootballInfo>
  <row>
    <ID>1</ID>
    <FirstName>Peter</FirstName>
    <LastName>The Keeper</LastName>
    <Club>NorthClub</Club>
    <CityID>1</CityID>
  </row>
  <row name="Row2">
    <ID>2</ID>
    <FirstName>Ivan</FirstName>
    <LastName>Mitov</LastName>
    <Club>EastClub</Club>
    <CityID>2</CityID>
  </row>
</FootballInfo>

<row>

<FirstName>Peter</FirstName>

<LastName>The Keeper</LastName>

<Club>NorthClub</Club>

</row>

<LastName>Mitov</LastName>

<Club>EastClub</Club>

</row>

</FootballInfo>

So, our task is three-fold:

Print the XML file within the FootballInfo node.
Print only the information of the clubs. E.g. NorthClub, EastClub etc.
Print only the node with name Row2.

Long story short, this is how the code looks like:

Option Explicit

Sub TestMe()

    Dim xmlObj As Object
    Set xmlObj = CreateObject("MSXML2.DOMDocument")

    xmlObj.async = False
    xmlObj.validateOnParse = False
    xmlObj.Load ("C:\Football.xml")

    Dim nodesThatMatter As Object
    Dim node            As Object

    Set nodesThatMatter = xmlObj.SelectNodes("//FootballInfo")
    For Each node In nodesThatMatter
'Task 1 -> print the XML file within the FootballInfo node:
        'Debug.Print node.XML
        Dim child   As Variant
        For Each child In node.ChildNodes
'Task 2 -> print only the information of the clubs.  E.g. NorthClub, EastClub etc.
            'Debug.Print child.ChildNodes.Item(3).XML
        Next child
    Next node
    
    Dim singleNode As Object
    Set singleNode = xmlObj.SelectSingleNode("//FootballInfo/row[@name='Row2']")
'Task 3 -> print only the node with name "Row2"
    'Debug.Print singleNode.XML
    
End Sub

Option Explicit

Sub TestMe()

Dim xmlObj As Object

Set xmlObj = CreateObject("MSXML2.DOMDocument")

xmlObj.async = False

xmlObj.validateOnParse = False

xmlObj.Load ("C:\Football.xml")

Dim nodesThatMatter As Object

Dim node As Object

Set nodesThatMatter = xmlObj.SelectNodes("//FootballInfo")

For Each node In nodesThatMatter

'Task 1 -> print the XML file within the FootballInfo node:

'Debug.Print node.XML

Dim child As Variant

For Each child In node.ChildNodes

'Task 2 -> print only the information of the clubs. E.g. NorthClub, EastClub etc.

'Debug.Print child.ChildNodes.Item(3).XML

Next child

Next node

Dim singleNode As Object

Set singleNode = xmlObj.SelectSingleNode("//FootballInfo/row[@name='Row2']")

'Task 3 -> print only the node with name "Row2"

'Debug.Print singleNode.XML

End Sub

It is a good example of a needed usage for late binding, as far as the “MSXML2.DOMDocument” library is probably not included by default in the VBA project. In order to make it run, simply uncomment the corresponding task. And fix the correct path to the .Load file.

Cheers! 🙂

Tagged with: singlenode, VBA, xml

VBA – Select a Single Node XML

Related posts: