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