DB Record To XML

Hello All

Does anyone have an "easy" way to convert a database records into an XML style file.

Ex. If I have a table Customer having fields FirstName, LastName,Address, Phone. I'd want to convert that to something like :

<Cusotmer>
<FirstName>Paul</FirstName>
<LastName>Smith</LastName>
<Address>555 Somewhere</Address>
<Phone>555-5555</Phone>
</Customer>
....
do this for each customer record in my database.

Once the file is made and FTP'd I'd use this to update a database in another location that is looking at this file. Is this feasable or is there a better way?

Thanks in Advance

I thought DTS allowed you to export from a table to XML format?

What kind of RDBMS are you using ?

Jon

Im thinking ahead sorry..yukon???

It aint gonna be so easy because of the fact that you need more than one record to be outputted.

How do you expect to display 2 records ?

Why not use linked servers?
What about bcp / dts ? You can move data much easier with these.

XML is nice (see woka's nice example on language support) however you dont need to go through all this work of outputting to XML and re importing to another db. Especially if you are using SQL Server. If you are using access..what about linked tables?

Are you using SQL Server 2000? If so, you can configure it so that the result is in XML format..... which you should be able to load into an XML Dom document and save to the harddrive..... having never done that, I don't know how it would work.... but....

you oculd loop through each record, for each field, and write out hte XML yourself... something like this:

VB Code:

Dim fld As ADODB.Field
 
  xml = xml & "<Customers>"
Do While Not rs.EOF
  xml = xml & "<Customer>"
  For Each fld In rs.Fields
    xml = xml & "<" & fld.name & ">" & fld.value & "</" & fld.Name & ">"
  Next
  xml = xml & "</Customer>"  rs.MoveNext
Loop
  xml = xml & "</Customers>"
'open file and save the XML....

TG

I actually came up with the same solution as techgnome. The problem i forsee is reconverting it from XML back to db records.

The database in question is indeed Access. Boss is tooooo cheap for anything else.

This is my scenario:

We have a software sitting in a head office type company. This company has x number of affiliates. The software generates reports on certain customers that are common to the affiliates. This report gets generated once a week.

We have another software installed in the affiliate offices that already download other files daily. I was trying to get this existing software to now download this XML type file so that I can convert it and repopulate their databases. Sounds wacky, I know, but most of it is built except for reconverting the XML to DB records.

Thx for the help jhermiz, techgnome

You could create a little utility prog in .NET which takes in the db and table names and outputs an xml file.
There are some great classes in .Net for doing this The dataset class has GetXML and GetXMLSchema methods.

The ADO Recordset can export itself to a file in XML format:

Save Method (ADO Recordset)


Saves (persists) the Recordset in a file.

Syntax

recordset.Save FileName, PersistFormat

Parameters

FileName Optional. Complete path name of the file where the Recordset is to be saved.

PersistFormat Optional. A PersistFormatEnum value that specifies the format in which the Recordset is to be saved. Can be one of the following constants.

Constant Description
adPersistADTG (Default) Save in proprietary Advanced Data Tablegram format.
adPersistXML Save in XML format.


Remarks

The Save method can only be invoked on an open Recordset. Use the Open method to later restore the Recordset from FileName.

If the Filter property is in effect for the Recordset, then only the rows accessible under the filter are saved. If the Recordset is hierarchical, then the current child recordset and its children are saved, but not the parent recordset.

The first time you save the Recordset, specify FileName. If you subsequently invoke Save, omit FileName or else a run-time error will occur. If you subsequently invoke Save with a new FileName, the Recordset is saved to the new file. However, the new file and the original file will both be open.

Save does not close Recordset or FileName, so you can continue to work with the Recordset and save your most recent changes. FileName remains open until the Recordset is closed, during which time other applications can read but not write to FileName.

For reasons of security, the Save method permits only the use of low and custom security settings from a script executed by Microsoft Internet Explorer. For a more detailed explanation of security issues, see the whitepaper titled, "Security Issues in the Microsoft Internet Explorer," at http://www.microsoft.com/data/techmat.htm.

If the Save method is called while an asynchronous Recordset fetch, execute, or update operation is in progress, then Save waits until the asynchronous operation is complete.

When the Save method is done, the current row position will be the first row of the Recordset.

For best results, set the CursorLocation property to adUseClient with Save. If your provider does not support all of the functionality necessary to save Recordset objects, the client cursor will provide that functionality.

Thanks for the info Dave Sell.

This should help. I will try a few test projects. Any chance you know about the reverse. From an xml file to an Access database. I did find some excellent code using a recursive function to get a complete XML file into a tree view control. Very fast and very good but nothing to a recordset in a database.

Thanks again.

Unfortunately, the XML output probably won't populate a Tree control as you might expect. I have heard the XML generated is quite ugly and horribly proprietary, very specific to the Recordset object.

There is a reverse function, allowing a Recordset to load an XML file generated by another Recordset, probably called the Load member function, used similarily, see MSDN.

Dave