How to read through an XML file with VBScript?

Question

Good Morning,

I am hoping someone can help me. I am trying to cycle through an xml file reading the values as I go. In the end, what I need to do is find a particular group of information and extract a piece of information from that. I know that's a little confusing so let me clarify.

Here is a sample of my xml file.

<?xml version="1.0"?>
<menu>
  <header>
    <listname>Nintendo GameCube</listname>
    <lastlistupdate>09/26/2017</lastlistupdate>
    <listversion>SupraKarma1.1</listversion>
  </header>
  <game name="007 - Agent Under Fire (USA)" index="true" image="0">
    <description>007: Agent Under Fire (USA)</description>
    <cloneof />
    <crc />
    <manufacturer>EA Games</manufacturer>
    <year>2002</year>
    <genre>Action</genre>
    <rating>ESRB - T (Teen)</rating>
    <enabled>Yes</enabled>
  </game>
  <game name="007 - Everything or Nothing (USA)" index="" image="">
    <description>007: Everything or Nothing (USA)</description>
    <cloneof />
    <crc />
    <manufacturer>EA Games</manufacturer>
    <year>2004</year>
    <genre>Action</genre>
    <rating>ESRB - T (Teen)</rating>
    <enabled>Yes</enabled>
  </game>
</menu>

Here is what I currently have to loop through and read the data.

Dim xmlDoc, GroupName, Games
Dim plot, GameName, GameRating

Set xmlDoc = CreateObject("Msxml2.DOMDocument")
xmlDoc.setProperty "SelectionLanguage", "XPath"
xmlDoc.load("C:\test.xml")`enter code here`

plot="No Value"

For Each GroupName In xmlDoc.SelectNodes("//menu")


    For Each Games In GroupName.SelectNodes("./game")
        GameName = Games.getAttribute("name")
        GameRating = Games.getAttribute("rating")

        MsgBox GameName & " ------- " & GameRating
    Next
Next

It is working to read the GameName but the GameRating is always blank. So my first question is what do I need to change to get the GameRating?

Second, it might be more efficient to somehow change the code to say I want to see the game rating where the name="007 - Agent Under Fire (USA)". How would that code look?

Thanks so much for any help.

Answer 1

Try this code

Set objXML = CreateObject("MSXML2.DOMDocument.6.0")
With objXML
    .SetProperty "SelectionLanguage", "XPath"
    .ValidateOnParse = True
    .Async = False
    .Load "C:\Users\pankaj.jaju\Desktop\test.xml"
End With

'Possible solution to Question 1
Set objNodes = objXML.SelectNodes("/menu/game/rating")
For Each objNode In objNodes
    MsgBox "Game Name = " & objNode.ParentNode.GetAttribute("name") & vbCrLf & "Game Rating = " & objNode.Text
Next

'Possible solution to Question 2
Set objNodes = objXML.SelectNodes("/menu/game[@name='007 - Agent Under Fire (USA)']/rating")
For Each objNode In objNodes
    MsgBox "Game Name = " & objNode.ParentNode.GetAttribute("name") & vbCrLf & "Game Rating = " & objNode.Text
Next

It is working to read the GameName but the GameRating is always blank. So my first question is what do I need to change to get the GameRating?

Your code is trying to look for rating attribute for game element which is incorrect. Unlike name, rating is also an element (child of game) and not an attribute of game. My solution is essentially finding the rating nodes (since you are mainly interested in these node) and then traversing back to its parent (game) to extract the gamename information.

External resource - https://www.w3schools.com/xml/xml_dtd_el_vs_attr.asp

Second, it might be more efficient to somehow change the code to say I want to see the game rating where the name="007 - Agent Under Fire (USA)". How would that code look?

My approach is again very similar to what I did for question 1, but now that you want filtered nodes, you have to tweak the xpath to select only the desired nodes. /menu/game[@name='007 - Agent Under Fire (USA)']/rating - the xpath will match only those game nodes which contains name attribute for the given value. Again, I am going directly to ratings node and traversing back to its parent.

Answer 2

You're very close, since, rating is in a text of a child node, not an attribute, the 1 liner fix you needed was:

GameRating = Games.selectSingleNode("rating").text

However, I had other issues with your script:

• You should add Option Explicit to help pick up any typos
• You should rename Games to Game since that reflects the XML node it corresponds to better
• You should rename GroupName to Menu since that matches the XML content better (in fact, you don't need this at all)
• You should use "/menu" not "//menu" in your XPath since you want it to match the top level node, and, infact, since there is only 1 top level node, you should use either selectSingleNode or documentElement instead of selectNodes.
• And, in fact, we can combine your menu XPath with your game XPath with /menu/game and do them both in 1 go, this is why we don't need GroupName
• I couldn't see the purpose of the plot variable, so, I removed it

Here are all the changes:

Dim xmlDoc, Game, GameName, GameRating

Set xmlDoc = CreateObject("Msxml2.DOMDocument")
xmlDoc.setProperty "SelectionLanguage", "XPath"
xmlDoc.load("C:\test.xml")

For Each Game In xmlDoc.SelectNodes("/menu/game")
REM For Each Game In xmlDoc.SelectNodes("/menu/game[@name='007 - Agent Under Fire (USA)']")
    GameName = Game.getAttribute("name")
    GameRating = Game.selectSingleNode("rating").text

    MsgBox GameName & " ------- " & GameRating
Next

To answer your second part, we modify the SelectNodes line to include an XPath query to pick the name you want. i.e.

REM For Each Game In xmlDoc.SelectNodes("/menu/game")
For Each Game In xmlDoc.SelectNodes("/menu/game[@name='007 - Agent Under Fire (USA)']")
    GameName = Game.getAttribute("name")
    GameRating = Game.selectSingleNode("rating").text

    MsgBox GameName & " ------- " & GameRating
Next