Save recordset as XML/Restore from XML?

Save recordset as XML/Restore from XML?

Post by Alastair Camero » Sun, 30 Jan 2000 04:00:00



Hi.

I want to store and restore recordsets as XML? I know ADO 2.1 has this
support.

However, what I'd like to do is craft a recordset by hand, save it as XML
but not as an external file on disk but to a string/variant property (I'm
using VB6, and Excel 97/2000) which I will subsequently store inside an
Excel .XLS file.

Can you tell me if ADO only supports writing to, and reading from, a disk
bound file containing a persisted recordset?

If so, I guess I'm going to have to save the recordset to a hidden XML file
on disk, read the contents of that file into a string, delete the disk file;
and when re-constituting the XML, save the XML string inside the Excel file
to a disk file, get ADO to read it, and then delete it.

Alastair

 
 
 

Save recordset as XML/Restore from XML?

Post by Michael Oz » Mon, 31 Jan 2000 04:00:00


if you can use ADO 2.5 (comes with Win2000) - then you will have greate
support for saving recordsets as XML in any object that supports IStream
interface. I am sure Excel does . You do something like this :
...
set obj = new < Excel.Worksheet ??? >
recSet.Save    obj, adPersistXML
...


Quote:> Hi.

> I want to store and restore recordsets as XML? I know ADO 2.1 has this
> support.

> However, what I'd like to do is craft a recordset by hand, save it as XML
> but not as an external file on disk but to a string/variant property (I'm
> using VB6, and Excel 97/2000) which I will subsequently store inside an
> Excel .XLS file.

> Can you tell me if ADO only supports writing to, and reading from, a disk
> bound file containing a persisted recordset?

> If so, I guess I'm going to have to save the recordset to a hidden XML
file
> on disk, read the contents of that file into a string, delete the disk
file;
> and when re-constituting the XML, save the XML string inside the Excel
file
> to a disk file, get ADO to read it, and then delete it.

> Alastair


 
 
 

1. limitations of FOR XML - saving xml message to message queue

Does anyone know a way to have a stored procedure
save an xml result set as a message queue?
Currently  I am kludging it by appending xml tags
to my result set, then looping through the result
set / cursor. It is working but but I am wondering
if there is a way to do it using FOR XML AUTO.  It
seems that FOR XML AUTO returns a stream object,
and this stream cannot be converted to a string
in the stored procedure.  It seems that FOR XML
is only good if you are going to be manipulating
the STREAM with VB or .NET.  I have to have the
stored procedure save the FOR XML stream directly
to the message queue. If anyone know a good way
please let me know.

I found this free extended procedure that saves messages to the
message queue.
http://www.sqlservercentral.com/products/xp_msmq/default.asp?scriptid=5

If you've tried doing this with sp_OACreate, you'll have a sorry tale
to tell. You get all the way to setting the body of the message, then
it all goes wrong  because the data type (variant) of the message body
property is incompatible with OLE Automation.

2. Error 2030: Update without edit or addnew when there IS edit-statement!

3. ADO + XML: Updating recordset from xml-document

4. T - SQL question

5. Persist recordset as XML, or cook XML w/ XMLDSO

6. Column not found in DBGrid

7. Saving Recordset as XML fails after 44KB

8. Determining table size

9. ADO Recordset Save and Open for XML

10. Save in XML does not work for hierarchical recordsets

11. Saving a recordset as an XML object

12. ADO Newbie: Saving and loading Recordsets to XML

13. How to save OLEDB recordset in XML file