vbscriptmsxmlrowset

XML rowset and MsXML2

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.

Solution

  • 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: