VBA – Read different levels of XML

After the article here for VBA – Select a Single Node XML now I am writing an update from it, which shows how to loop through all the nodes that we are interested in and get data from them.

The XML is quite similar with the first one, but one level deeper – the clubs now have a coach, a manager and establishment year:

<FootballInfo>
  <row>
    <ID>1</ID>
    <FirstName>Peter</FirstName>
    <LastName>The Keeper</LastName>
    <Club name ="NorthClub">
		<ClubCoach>Pesho</ClubCoach>
		<ClubManager>Partan</ClubManager>
		<ClubEstablishedOn>1994</ClubEstablishedOn>
	</Club>
    <CityID>1</CityID>
  </row>
  <row name="Row2">
    <ID>2</ID>
    <FirstName>Ivan</FirstName>
    <LastName>Mitov</LastName>
    <Club name = "EastClub">
		<ClubCoach>Gosho</ClubCoach>
		<ClubManager>Goshan</ClubManager>
		<ClubEstablishedOn>1889</ClubEstablishedOn>
	</Club>
    <CityID>2</CityID>
  </row>
</FootballInfo>

The idea is to put the club name on the console and the club data like this:

NorthClub
ClubCoach
Pesho

ClubManager
Partan

ClubEstablishedOn
1994

EastClub
ClubCoach
Gosho

ClubManager
Goshan

ClubEstablishedOn
1889

Using late binding and the Football.xml xml file, we start looping. The looping is quite normal, the only “hard” time is probably getting the named item “name” from the third child. This is actually 90% the reason why I wrote this article:

Debug.Print level2.ChildNodes.Item(3).Attributes.getNamedItem("name").Text

The whole code looks like this:

Option Explicit

Sub TestMe()

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

    xmlObj.async = False
    xmlObj.validateOnParse = False
    xmlObj.Load (ThisWorkbook.Path & "\Football.xml")

    Dim nodesThatMatter As Object
    Dim node            As Object
    Set nodesThatMatter = xmlObj.SelectNodes("//FootballInfo")
    
    Dim level1 As Object
    Dim level2 As Object
    Dim level3 As Object
    
    For Each level1 In nodesThatMatter
        For Each level2 In level1.ChildNodes
            Debug.Print level2.ChildNodes.Item(3).Attributes.getNamedItem("name").Text
            For Each level3 In level2.ChildNodes.Item(3).ChildNodes
                Debug.Print level3.BaseName
                Debug.Print level3.Text & vbCrLf
            Next
        Next
    Next    
End Sub

Well, this is it! The code compiles, the sun is shining outside and it is August!