XML rowset and MsXML2

Question

I have a rowset coming back using the Microsoft rowset schema from my stock control system.

However when using msxml2 to read the document I don't seem to be able to access the data (Written in vbscript)

<xml>
    <s:schema>
        <!-- Schema here -->
    </s:schema>
    <rs:data>
        <z:row field="value" field1="value" />
    </rs:data>
</xml>

To pull this back I am using:

Set objXmlHttp = Server.CreateObject("Msxml2.ServerXMLHTTP")
objXmlHttp.open "POST", address, False
objXmlHttp.setRequestHeader "Content-Type", "text/xml"
objXmlHttp.Send strXml

Set objLst = XML_response.getElementsByTagName("data")
myValue = objLst.item(0).getAttribute("field")

However I am receiving the following message:

Microsoft VBScript runtime error '800a01a8'

Object required: 'objLst.item(...)'

This is probably me doing something totally wrong, if so could someone point it out to me please because I have stared at this for 2 hours now and I can't get it.

I know this isn't _exactly_ what you want, but if the XML is the same schema that the ADODB Recordset uses you could load the XML object straight into a recordset object: `dim rs: set rs = CreateObject("ADODB.RecordSet"): rs.open XML_Response` — , Jan 30 '12 at 13:33

score 1 · Accepted Answer · answered Jan 27 '12 at 15:27

Given a table like:

SELECT TOP 5 * FROM [actor.txt]
----------------------------------------------------------------
|actor_id|first_name|last_name   |last_update         |
|       1|PENELOPE  |GUINESS     |2/15/2006 4:34:33 AM|
|       2|NICK      |WAHLBERG    |2/15/2006 4:34:33 AM|
|       3|ED        |CHASE       |2/15/2006 4:34:33 AM|
|       4|JENNIFER  |DAVIS       |2/15/2006 4:34:33 AM|
|       5|JOHNNY    |LOLLOBRIGIDA|2/15/2006 4:34:33 AM|
----------------------------------------------------------------

in an ADO (classic, tested with version 2.8) accessible database, you can save the resultset to XML using

  oRS.Save sFSpec, adPersistXML

That gives you XML like:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
    xmlns:rs='urn:schemas-microsoft-com:rowset'
    xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly'>
        <s:AttributeType name='actor_id' rs:number='1' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
            <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='first_name' rs:number='2' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
            <s:datatype dt:type='string' dt:maxLength='45'/>
        </s:AttributeType>
        <s:AttributeType name='last_name' rs:number='3' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
            <s:datatype dt:type='string' dt:maxLength='45'/>
        </s:AttributeType>
        <s:AttributeType name='last_update' rs:number='4' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
            <s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:extends type='rs:rowbase'/>
    </s:ElementType>
</s:Schema>
<rs:data>
    <z:row actor_id='1' first_name='PENELOPE' last_name='GUINESS' last_update='2006-02-15T04:34:33'/>
    <z:row actor_id='2' first_name='NICK' last_name='WAHLBERG' last_update='2006-02-15T04:34:33'/>
    <z:row actor_id='3' first_name='ED' last_name='CHASE' last_update='2006-02-15T04:34:33'/>
    <z:row actor_id='4' first_name='JENNIFER' last_name='DAVIS' last_update='2006-02-15T04:34:33'/>
    <z:row actor_id='5' first_name='JOHNNY' last_name='LOLLOBRIGIDA' last_update='2006-02-15T04:34:33'/>
</rs:data>
</xml>

To read that data, start with (local, console) code like:

  Dim oFS    : Set oFS   = CreateObject( "Scripting.FileSystemObject" )
  Dim sDDir  : sDDir     = oFS.GetAbsolutePathName( "..\Data" )
  Dim sFSpec : sFSpec    = oFS.GetAbsolutePathName( "..\Data\actor.xml" )
  Dim oXDoc  : Set oXDoc = CreateObject("msxml2.domdocument")
  Dim sXPath, ndFnd, ndlFnd, attrX, nIdx
  oXDoc.async             = False
  oXDoc.validateOnParse   = False
  oXDoc.resolveExternals  = False
  oXDoc.setProperty "SelectionLanguage", "XPath"
  oXDoc.setProperty "SelectionNamespaces", Join( Array( _
      "xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'" _
    , "xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'" _
    , "xmlns:rs='urn:schemas-microsoft-com:rowset'" _
    , "xmlns:z='#RowsetSchema'" _
 ), " ")
  If oXDoc.load(sFSpec) Then
     WScript.Echo sFSpec, "looks good."
     For Each sXPath In Array( _
              "/xml" _
            , "/xml/s:Schema" _
            , "/xml/rs:data" _
            , "/xml/rs:data/z:row[@actor_id=""2""]" _
         )
         WScript.Stdout.Write "|" & sXPath & "| => "
         Set ndFnd = oXDoc.selectSingleNode( sXPath )
         If ndFnd Is Nothing Then
            WScript.Stdout.WriteLine "not found"
         Else
            WScript.Stdout.WriteLine "found a(n) " & ndFnd.tagName
         End If
     Next
     WScript.Echo "-----------------------"

'<rs:data>
'   <z:row actor_id='1' first_name='PENELOPE' last_name='GUINESS' last_update='2006-02-15T04:34:33'/>
'       ...
'</rs:data>
     sXPath = "/xml/rs:data/z:row[@actor_id=""3""]"
     Set ndFnd = oXDoc.selectSingleNode( sXPath )
     If ndFnd Is Nothing Then
        WScript.Echo "|", sXPath, "| not found"
     Else
        For Each attrX In ndFnd.Attributes
            WScript.Echo attrX.Name, attrX.Value
        Next
     End If
     WScript.Echo "-----------------------"

     sXPath = "/xml/rs:data/z:row"
     Set ndlFnd = oXDoc.selectNodes( sXPath )
     If ndlFnd Is Nothing Then
        WScript.Echo "ndlFnd Is Nothing"
     Else
        If 0 = ndlFnd.Length Then
           WScript.Echo "ndlFnd is empty"
        Else
           For Each ndFnd In ndlFnd
               WScript.Echo TypeName(ndFnd)
               For Each attrX In ndFnd.Attributes
                   WScript.Echo "", attrX.Name, attrX.Value
               Next
           Next
        End If
     End If
  Else
     WScript.Echo "Bingo!"
     WScript.Echo oXDoc.parseError.reason
  End If

The important steps:

For developing/testing avoid the extra complexity of using a server; if cscript readxml.vbs succeeds, it's easy to 'port' the working code to .asp (and deal with server specific problems separately)
Create an msxml2.domdocument
Configure it; especially copy the namespaces from the xml tag to the SelectionNamespaces
Load the file, check for errors
Specify your XPath expressions (carefully!, try to 'translate' XML from your sample to XPATH)
Check the results of .selectSingleNode() and .selectNodes()
Access the attributes holding the data

I added my remote code at the top instead of the static content, modified it to find what I wanted and the result Bingo!. Thank you very much. — HAWood, Jan 27 '12 at 15:51

score 1 · Answer 2 · answered Jan 27 '12 at 18:18

Lets assume the XML you posted a) includes correct namespace aliases and b) that it has loaded in XML_response correctly.

Now the "correct" way to do this is use XPath however we can achieve our goals with simpler code in this case. The default selection language in MSXML (3 or below) is XSL Pattern, this selection doesn't understand namespace aliases, so the <rs:data> has the tag name "rs:data" not "data" from its point of view. So thats the first thing you are doing wrong.

Another thing is that getElementsByTag is returning a collection of nodes, which contains just one node the "rs:data". Your code is trying to read the "field" attribute from that node where in fact it on a child node of "z:row".

Here is what you code should look like:

Dim objData: Set objData = XML_response.documentElement.selectSingleNode("rs:data") 

Dim row
Dim fieldVal, field1Val
For Each row in objData.selectNodes("z:row")
     fieldVal = row.getAttribute("field")
     field1Val = row.getAttribute("field1")

     ''# Do stuff with your fields for each row found.
Next

This is exactly my original method, however it turns out it wasn't the method but the way the xml was returned. The documentation didn't state it but the rowset xml was wrapped by another xml document meaning I had to pull our the inner xml first and it worked finally. The reason I hadn't spotted this originally was because when I used loadXML it parsed it just fine and only returning the inner xml even though it was actually being malformed without throwing errors. A very very strange occurance but it worked thank you guys for your help. — HAWood, Jan 30 '12 at 12:47

XML rowset and MsXML2

2 Answers2