Reading XML Stream into an ADO Recordset

Hi,
My application requires orders to be sent from a branch office to the central office and back. To accomplish this, I am currently exporting various tables into CSV format, sending via HTTP, and re-inserting them into the database on the other end. My problem is that there are many central offices and even more branches, and each has its own database. Now the database format changes from time to time because of enhancements and bug fixes, and when they do, the routines that import the CSV files are likely to break because the data is in the incorrect order or whatever. This is a constant source of headache and frustration. I would like to use the Save method of the Recordset object to save the recordset to a stream as XML. This works no problem, but I am uncertain how to proceed with the other end of this conversation which is to convert the XML Stream (Recordset) back into a Recordset, or better into a SQL insert statement. I am using Access and SQL Server databases, so I prefer a solution that works as independent of the underlying data source as possible. I am trying for a solution that frees my code from having to maintain any knowledge about the database schemas on either end.

Can someone please tell be how to get the Stream containing the Recordset XML back into the other database on the receiving end of the conversation ?

Thanks to anyone who can help.

[Lothar Haensler]

I don't know of a solution that's independent from the underlying database.
But, SQLServer 2000 offers an SQL extension "OpenXML" that allows you to accept XML as input to a stored procedure. You can then manipulate (insert/update) that XML data.

Thanks, for the reply. That sounds exactly like what I need, only It has to be more portable.

I must support Access, as well as SQL Server version 6.5 and higher. If i have to code the solution myself that is okay, but I am unsure how to get the XML back into the database at the other end.

If I had a or could develop a COM object that knew of an ADO.Connection and a File or Stream containing an ADO Recordset as XML, and the COM object could put the XML Recordset into the DataSource identified by the ADO.Connection I would be most pleased.

I think all of this is within my programming ability except for the part about making the XML go into the target database. If you know of any sample code that can do this, or have any suggestions about how to proceed, I would rate your post highly.

Thanks again.

[Lothar Haensler]

>If you know of any sample code that can do this, or have any suggestions about how to proceed, I would rate your post highly.

Nice try :-)
1. Anonymous posters can't rate, as far as I know.
2. If I had such a solution, I would sell it for lots of money.

[john_lewicki]

Check out the House Of COM article in MSDN magazine July 2000. Its on the MSDN website.
Its entitled 'Using ADO to create XML based recordsets'. It might be helpful.

-John