XML To Recordset

[nickdel]

Basically I have a fairly large xml file and all I'm wanting to achieve is add some sort of paging to this so if you have a better solution feel free to chip in!

I thought the best way to achieve the above would be to load the XMl to a recordset however no matter what I try I get the following error:

Recordset cannot be created. Source XML is incomplete or invalid.

My XML is valid, I've checked! (Here's a small snippet)

<?xml version="1.0"?><!DOCTYPE Root[ <!ELEMENT Root (SearchData+, TableDetail+)> <!ELEMENT SearchData (SearchQuery, SearchT)> <!ELEMENT TableDetail (TableOID, TableName, ShortDesc, TableType)> <!ELEMENT SearchQuery (#PCDATA)> <!ELEMENT SearchT     (#PCDATA)> <!ELEMENT TableOID  (#PCDATA)> <!ELEMENT TableName (#PCDATA)> <!ELEMENT ShortDesc (#PCDATA)> <!ELEMENT TableType (#PCDATA)> ]>
<Root>
<SearchData>
<SearchQuery>acc</SearchQuery>
<SearchT>29/01/2008</SearchT>
</SearchData>
<TableDetail>
<TableOID>1</TableOID>
<TableName>AccessControl</TableName>
<ShortDesc>This is a test message to add to a field that should be no more than fifty characters long...</ShortDesc>
<TableType>Mapping</TableType>
</TableDetail>
</Root>

And here's my code (pretty much straight from msdn)

Public Function RecordsetFromXMLString(sXML)

    Dim oStream
    Set oStream = server.createobject("ADODB.Stream")
    
    oStream.Open
    oStream.WriteText sXML   'Give the XML string to the ADO Stream

    oStream.Position = 0    'Set the stream position to the start
	
    Dim oRecordset
    Set oRecordset = server.createobject("ADODB.Recordset")
 
       
    oRecordset.Open oStream    'Open a recordset from the stream

    oStream.Close
    Set oStream = Nothing

    Set RecordsetFromXMLString = oRecordset  'Return the recordset

    Set oRecordset = Nothing

End Function

Error happens on this line:

oRecordset.Open oStream

Any help, much appreciated as this has been doing my head in for hours!

Cheers
Nick

where would we be without rhetorical questions...

 

[xwb]

Try the XML without the DTD.

 

[tsuji]

The .writetext sxml will succeed only:
[tt]quote
Provided the XML data is in the format where ADO recordset can accept.
unquote[/tt]
ref:

http://support.microsoft.com/kb/263247

The way to convince oneself is to do the circle.
[1] Use some db you have and sql to an instance of adodb.recordset;
[2] .save "abc.xml", adPersistXML 'adPersistXML=1
[3] Use fso to load up the "abc.xml", and .readall to the sXML.
[4] Now, use that sXML to feed to the adodb.stream.

You can also inspect the saved abc.xml with text editior to discover the "raw" format the ado can accept.

 

[tsuji]

Amendment
When I said
>self: The .writetext sxml will succeed only:
I actually meant
[tt]The [red].open ostream[/red] will succeed only:[/tt]