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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
1 |
Debug.Print level2.ChildNodes.Item(3).Attributes.getNamedItem("name").Text |
The whole code looks like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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!