Getting XML attribute based on another attribute in VBScript

Question

I have the following XML that has been return from a SOAP API:

<?xml version="1.0" encoding="UTF-8"?>
<results>
<result>
<field name="accountnumber" value="100035" />
<field name="occupantcode" value="11" />
<field name="otherfield" value"do not care about this one" />
</result>
</results>

I am trying to load the account number into a variable called AcctNum, and the other one into OccCode using VBScript. I am very new to XPath queries. I am trying something like this:

Set ANumNode = xmlResponse.SelectNodes("//results/result/field[@accountnumber]")
For Each objSite In ANumNode
  AcctNum = objSite.SelectSingleNode("accountnumber").Text
Next

but of course that is blowing up spectacularly. There are actually about 20 field nodes in the response, of which I am only concerned with about 4 of them. I cannot change the format of the XML being outputted.

I can only offer you a partial answer, since my VB Script is more than rusty. To get the account number the xpath used would (could) be "//field[@name='accountnumber']" and correspondingly "//field[@name='occupantcode']" which would return the nodes accordingly, from which you should be able to extract the text values. Anything beyond that would be me guessing. Hope it helps. — Bill Hileman
– Bill Hileman, Commented Apr 17, 2017 at 20:49
"Blowing up spectacularly" is a little unspecific. What exactly doesn't work as expected? What result did you expect? What result did you actually get? Did you receive an error? What does it say? — Ansgar Wiechers
– Ansgar Wiechers, Commented Apr 17, 2017 at 22:00
"Blowing up spectacularly" in this case is returning no results after trying multiple time. — dcoughler
– dcoughler, Commented Apr 18, 2017 at 12:58

Ansgar Wiechers · Accepted Answer · 2017-04-22 22:22:54Z

Your XML is not well-formed.
You need to understand the diffs between nodes and attributes

Use the docs (start here) to work thru this sample code:

Option Explicit

Dim oFS    : Set oFS  = CreateObject( "Scripting.FileSystemObject" )
Dim sFSpec : sFSpec   = oFS.GetAbsolutePathName(".\43459134.xml")
Dim oXml   : Set oXml = CreateObject("Msxml2.DOMDocument")

oXml.setProperty "SelectionLanguage", "XPath"
oXml.async = False
oXml.load sFSpec

If 0 = oXml.parseError.errorCode Then
   WScript.Echo "loaded:", sFSpec
   WScript.Echo "root:", oXml.documentElement.tagName

   Dim sXPath, ndlResults, ndResult

   sXPath = "/results/result"
   Set ndlResults = oXml.selectNodes(sXPath)
   If 0 = ndlResults.length Then
      WScript.Echo "no '" & sXPath & "' found"
   Else
      WScript.Echo "found", ndlResults.length, "node(s) for '" & sXPath & "'"
      For Each ndResult In ndlResults
         WScript.Echo "ndResult.tagName:", ndResult.tagName
         Dim AcctNum : AcctNum = ndResult.selectSingleNode("field[@name=""accountnumber""]").getAttribute("value")
         Dim OccCod  : OccCod  = ndResult.selectSingleNode("field[@name=""occupantcode""]").getAttribute("value")
         WScript.Echo AcctNum, OccCod
      Next
   End If
Else
   WScript.Echo "errorCode:", oXml.parseError.errorCode
   WScript.Echo oXml.parseError.reason
End If

Output:

cscript 43459134.vbs
loaded: E:\work\proj\soa\tmp\43459134.xml
root: results
found 1 node(s) for '/results/result'
ndResult.tagName: result
100035 11

Update wrt comments:

The script 'works' for the (corrected) .xml you posted; if /results/result is located further down the tree, try /REALDOCROOT/pi/pa/pi/results/result or the less specific //results/result. Or else: post the real input.

Unfortunately, I get "no /result/results found". That was after taking the live response saving it to a file, and reading it back in.

Ansgar Wiechers · Accepted Answer · 2017-04-22 22:25:01Z

0

I ended up abandoning XPath queries and instead built my own parser since the structure was going to be static:

Function retrieveValue(XMLblock, fieldname)
  start = 1
  '***Find field
  start = InStr(start, XMLBlock, "" & fieldname & "")
  '***Find value tag
  start = InStr(start, XMLBlock, "value")
  '***Find end quote for value
  endquote = InStr(start+7, XMLBlock, """")
  retrieveValue = Mid(XMLBlock, start+7, endquote-start-7)
End Function

Not the most elegant code, but it works so that I can continue on with my project. Thank you everyone for your assistance.

edited Apr 22, 2017 at 22:25

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

answered Apr 18, 2017 at 13:32

dcoughler

612 silver badges11 bronze badges

1 Comment

Ansgar Wiechers Over a year ago

This isn't just "not the most elegant code". It's arguably the worst possible approach you could've taken.

Collectives™ on Stack Overflow

Getting XML attribute based on another attribute in VBScript

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related