VB/WSH Read simple XML spreadsheet

[eean]

Hello,

 

I am trying to write a simple script to read the values on each line in an XML file, do some other stuff, then move onto the next line. The file is produced by SIMS.

 

The XML file

 
   
     
       
         
           
             
               
               
               
               
             
           
         
       
     
   
 
 
   Dylan
   Bob
   A0011
   America
 
 
   Lennon
   John
   A00088
   United Kingdom
 

 

 

Here's my code:

Set xmlDoc = CreateObject("Microsoft.XMLDOM")

xmlDoc.Load("z:\test.xml")

Set NodeList = XMLDoc.documentElement.selectNodes("/SuperStarReport/Record")

For i = 0 To NodeList.length - 1
   WScript.Echo NodeList(i).getAttribute("Forename")
Next

 

All I get is 'null' over and over. I've tried various combinations. What am I doing wrong?

 

Thanks in advance.

[eean]

Ok, I have got a little bit closer.

I can get it so it reads a record all as one line. eg.

Dylan Bob A0011 United Kingdom

 

Set xmlDoc = CreateObject("Microsoft.XMLDOM")

xmlDoc.Async = "False"
xmlDoc.Load("z:\test2.xml")

Set NodeList = XMLDoc.selectNodes("//SuperStarReport/Record")

For Each Node in NodeList
WScript.Echo Node.Text
   WScript.Echo Node.Attributes.getNamedItem("Forename").Text
Next

 

The line Node.Text works, but the second line where I try and get a specific value it still fails: 'Object Required'.

 

Any help?

Edited February 4, 2016 by eean

[spadam]

Just a guess really, hard to test on my phone, but it looks to me like your code is trying to access an attribute of the record node. The record node has no attributes so it returns null. Try something like Node.item["Forename"] instead. Check the api to see the properties and methods available.

[chrisbsc]

I have the following code that i think is what you require

 

Set xmlDoc = CreateObject("Microsoft.XMLDOM")

 

xmlDoc.Async = "False"

xmlDoc.Load("z:\test2.xml")

 

Set colNodes=xmlDoc.selectNodes("/SuperStarReport/Record")

 

For Each objNode in colNodes

Wscript.Echo objNode.Text

 

Set cnode = objNode.childnodes

For Each c In cnode

temp =""

node1 = c.nodename

text1 = c.text

temp = temp & node1 & "=" & text1

Wscript.Echo temp

next

Next

[eean]

@spadam I wish is were as simple as looking at the API - I've tried so many! My code is from the Microsoft guide. Just doesn't seem to work.

 

Thanks @chrisbsc That might work - I'll try tomorrow.

 

I was hoping to get the text/value of a specific node, calling it by name rather than relying on the order of the columns. It seems cleaner - if something were to change (e.g. the order of the columns) it wouldn't mess it up.

[chrisbsc]

I have amended the code to use a specific node as required

Set objXMLDoc  = CreateObject("Microsoft.XMLDOM")



objXMLDoc.Async = "False"

objXMLDoc.Load("z:\test2.xml")

Set colNodes=objXMLDoc.selectNodes("/SuperStarReport/Record")



For Each objNode in colNodes



LegalSurname = objNode.getElementsByTagName("LegalSurname").item(0).text

Forename = objNode.getElementsByTagName("Forename").item(0).text

AdmissionNumber = objNode.getElementsByTagName("AdmissionNumber").item(0).text

issuing_nation = objNode.getElementsByTagName("issuing_nation").item(0).text



Wscript.Echo LegalSurname

Wscript.Echo Forename

Wscript.Echo AdmissionNumber

Wscript.Echo issuing_nation

Next

[/Code]

Edited February 4, 2016 by chrisbsc

[eean]

Thanks @chrisbsc.

 

Your first script works as expected. The second script doesn't, unfortunately. I'm getting the same error that I get with every example script on the internet!

 

Object Required:

'objNode.getElementsByTagName(...).item(...)'

Code: 800A01A8

Source: Microsoft VBScript Runtime Error

 

I can achieve what I want to do now, thanks to your help. However, I'd quite like to figure out second solution as it seems much better.

[chrisbsc]

Are you using the code in a VBS script or webpage?

If the answer is a webpage what Browser are you using.

[eean]

It's a VBS script in Windows Script Host.

[chrisbsc]

Try

Set objXMLDoc  = CreateObject("Microsoft.XMLDOM")

objXMLDoc.Async = "False"
objXMLDoc.Load("z:\test2.xml")
Set colNodes=objXMLDoc.selectNodes("/SuperStarReport/Record")

For Each objNode in colNodes

LegalSurname = objNode.getElementsByTagName("LegalSurname").item(objNode.getElementsByTagName("LegalSurname").length -1).text
Forename = objNode.getElementsByTagName("Forename").item(objNode.getElementsByTagName("LegalSurname").length -1).text
AdmissionNumber = objNode.getElementsByTagName("AdmissionNumber").item(objNode.getElementsByTagName("LegalSurname").length -1).text
issuing_nation = objNode.getElementsByTagName("issuing_nation").item(objNode.getElementsByTagName("LegalSurname").length -1).text

Wscript.Echo LegalSurname
Wscript.Echo Forename
Wscript.Echo AdmissionNumber
Wscript.Echo issuing_nation
Next