VBScript to assign variables from XML nodes

Question

I am trying to write a simple VBScript that looks into an XML file, pulls out two attributes, adds them, and the result to an output file. So far I have been able to load the XML input file using:

Option Explicit
Set objDoc = CreateObject("MSXML.DOMDocument")
objDoc.Load "C:\_STATS_SCRIPTS\STATS_HOME.xml"

Then I created and initialized variables like this:

Dim fumlost
Dim intercept
Dim turnovers
fumlost="0"
intercept="0"
turnovers="0"

Here is what my XML looks like (truncated after the totals subtree):

<fbgame source="Tas Football" version="4.16.01" generated="9/3/2014">
    <venue gameid="01UA-WVU" visid="WVU" homeid="UA" visname="West Virginia" homename="Alabama" date="8/30/2014" location="Atlanta, Georgia" stadium="Georgia Dome" start="3:36" end="7:05" neutralgame="Y" duration="3:29" attend="70502" temp="" wind="" weather="Indoor">
        <officials ref="David Epperly" ump="Mike Webster" line="Steve Clein" lj="Rod Pearson" bj="Pat Ryan" fj="Mike Culler" sj="Eddie Bonet"></officials>
        <notes>
            <note text="Replay: Dan Post"></note>
        </notes>
        <rules qtrs="4" mins="15" downs="4" yds="10" kospot="35" tbspot="20" kotbspot="25" patspot="3" safspot="20" td="6" fg="3" pat="1" patx="2" saf="2" defpat="2" rouge="1" field="100" toh="3" sackrush="Y" fgaplay="Y" netpunttb="Y"></rules>
    </venue>
    <team vh="H" code="8" id="UA" name="Alabama" record="1-0" abb="A">
        <linescore prds="4" line="3,17,10,3" score="33">
            <lineprd prd="1" score="3"></lineprd>
            <lineprd prd="2" score="17"></lineprd>
            <lineprd prd="3" score="10"></lineprd>
            <lineprd prd="4" score="3"></lineprd>
        </linescore>
        <totals totoff_plays="82" totoff_yards="538" totoff_avg="6.6">
            <firstdowns no="30" rush="13" pass="14" penalty="3"></firstdowns>
            <penalties no="7" yds="49"></penalties>
            <conversions thirdconv="9" thirdatt="15" fourthconv="0" fourthatt="1"></conversions>
            <fumbles no="0" lost="0"></fumbles>
            <misc yds="0" top="37:47" ona="0" onm="0" ptsto="0"></misc>
            <redzone att="4" scores="4" points="24" tdrush="3" tdpass="0" fgmade="1" endfga="0" enddowns="0" endint="0" endfumb="0" endhalf="0" endgame="0"></redzone>
            <rush att="49" yds="288" gain="294" loss="6" td="3" long="26"></rush>
            <pass comp="24" att="33" int="1" yds="250" td="0" long="38" sacks="0" sackyds="0"></pass>
            <rcv no="24" yds="250" td="0" long="38"></rcv>
            <punt no="2" yds="101" long="62" blkd="0" tb="0" fc="1" plus50="1" inside20="1" avg="50.5"></punt>
            <ko no="7" yds="453" ob="0" tb="3"></ko>
            <fg made="4" att="4" long="47" blkd="0"></fg>
            <pat kickatt="3" kickmade="3"></pat>
            <defense tackua="34" tacka="38" tot_tack="72" tflua="6" tfla="0" tflyds="30" sacks="3" sackyds="25" brup="3"></defense>
            <kr no="4" yds="99" td="0" long="26"></kr>
            <pr no="1" yds="-1" td="0" long="0"></pr>
            <scoring td="3" fg="4" patkick="3"></scoring>
        </totals>

What I need to do next is assign /fbgame/team/totals/fumbles@lost to my fumbles variable, assign /fbgame/team/totals/pass@int to my intercept variable, and then add the two together to make turnovers, then output. I think I can handle the summing of variables and outputting a file, but I'm lost on how to get the XML attribute assigned to my variables. Earlier I successfully made a script that uses sXPath to split visiting team and home team out of my main input file, but I am currently unable to use what I learned there to get this task done!

I am very appreciative of any help that comes my way, as I am a n00b scripter and in a little over my head!

matthias_h · Accepted Answer · 2014-09-04 19:57:36Z

1

Not sure about using XPath in VBScript, but following XPath works for me:

string(//fbgame/team/totals/fumbles/@lost)

Result: 0

string(//fbgame/team/totals/pass/@int)

Result: 1

Maybe this works for your approach or you can adjust it further.

In case you'd need the whole node and not only the value, following XPath

//fbgame/team/totals/fumbles[@lost]

results in

 <fumbles no="0" lost="0" />

For providing completeness - depending on the query, //fbgame could be /fbgame. I just adjusted your XML-part to be valid for parsing and let the XPath match every fbgame (as the example only contains one game).

In case question wasn't about XPath expression but about how to get XPath values in VBScript, this should do it (at least getting the value, guess you would store the values in variables for doing the math later on):

For Each a In objDoc.selectNodes ("//fbgame/team/totals/fumbles/@lost")
 Wscript.Echo a.text
Next

For Each b In objDoc.selectNodes ("//fbgame/team/totals/pass/@int")
 Wscript.Echo b.text
Next

edited Sep 4, 2014 at 19:57

answered Sep 4, 2014 at 19:25

matthias_h

11.4k9 gold badges24 silver badges40 bronze badges

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

2 Comments

matthias_h Over a year ago

don't mind about the formatting, guess I get it ;) prob is to judge the result without knowing the XML, but wouldn't CInt(a) for a as string value 01 format to integer 1..? Hope that's no misunderstanding, just think that's the problem right now. Or can you already add up the results?

Jake Ballard Over a year ago

I can't add the two variables. I think it's just putting them next to each other when I do "turnovers = intercept + fumlost"

Jake Ballard · Accepted Answer · 2014-09-04 20:04:58Z

0

The code below is pulling values correctly, but when summing to 'turnovers' the returned output is "01" instead of just "1". Is this correct??

Set colNodes=xmlDoc.selectNodes ("//fbgame/team/totals/pass/@int")

For Each objNode in colNodes
  intercept = objNode.text
  Wscript.Echo intercept 
Next

Set colNodes=xmlDoc.selectNodes ("//fbgame/team/totals/fumbles/@lost")

For Each objNode in colNodes
  fumlost = objNode.text
  Wscript.Echo fumlost
Next

turnovers = fumlost + intercept

Wscript.Echo turnovers

answered Sep 4, 2014 at 20:04

Jake Ballard

436 bronze badges

5 Comments

Jake Ballard Over a year ago

If I had to guess, I'd say I need to convert the variables to integers. I'm now looking for how to do that...

matthias_h Over a year ago

just have a look at my recent comment below, maybe that's it (in case it's CInt()

matthias_h Over a year ago

next thing is - guessing here: you already have two variables; just found for addition you'd have to set a 3rd, like: Dim Var3 - and then Var3 = Var1 + Var2 for getting the result in Var3; maybe you just have to dim intercept + fumlost first, too? e.g. like here: tutorialspoint.com/vbscript/vbscript_variables.htm

Jake Ballard Over a year ago

turnovers = CInt(fumlost) + CInt(intercept) ' BOOM '

matthias_h Over a year ago

glad to be of help, good luck + thx for the question; didn't knew about vbscript xpath before, so seen something new

Jake Ballard · Accepted Answer · 2014-09-04 20:43:16Z

Thanks for the help! Couldn't have done this without stackoverflow! Final working code:

Set xmlDoc = CreateObject("Microsoft.XMLDOM")

xmlDoc.Async = "False"
xmlDoc.Load("STATS_HOME.xml")


Dim fumlost
Dim intercept
Dim turnovers
fumlost="0"
intercept="0"
turnovers="0"

Set colNodes=xmlDoc.selectNodes ("//fbgame/team/totals/pass/@int")

For Each objNode in colNodes
  intercept = objNode.text
Next

Set colNodes=xmlDoc.selectNodes ("//fbgame/team/totals/fumbles/@lost")

For Each objNode in colNodes
  fumlost = objNode.text
Next
turnovers = CInt(fumlost) + CInt(intercept)

Dim fso
Set fso = WScript.CreateObject("Scripting.Filesystemobject")
Set f = fso.OpenTextFile("C:\_STATS_SCRIPTS\TO_HOME.txt", 2)
f.Writeline turnovers

Collectives™ on Stack Overflow

VBScript to assign variables from XML nodes

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related