Hi,
I am looking for a way to export from a couple of databases (SQL Server 2000 and Oracle 9i) to XML. Does anyone have any simple suggestions

Thanks
Hamish

try looking up information on DTS. I has an XML Task.

I have seen that it has XML support, but when I tried using the Data export wizard, I could only export to another database or datasource, I did not have an XML option. I was thinking I might have to create a DTS package and use some VBScript to do it. But I have never used DTS packages, so that will be a bit of a learning curve.

I am using SQL Server 2000.

I think you'll find a method here:
http://www.sqlxml.org/faqs.aspx?faq=29

for SqlServer
if you only need some queries to be exported to Xml,
you can end your query in QA with "for xml auto" and output the result to a file

Ahmed Ali
Software Developer

Have you tried this? If you did you'd know that if you try to set up a DTS task where the source is a FOR XML query and the destination is a file then a binary stream gets written to the text file.

What exactly do you mean? Do you want the schema, tables and columns, stored procs etc., or all the data or both? What is going to use the result? I ask this because it makes a difference to how the original export is formatted.

Joe (MVP - xml)

Thanks for your replies. I should have been a bit more specific, all I am after is the data from various tables. eg if I have a 'Customers' table with a 'FirstName' field and 'LastName' field, I would want something like:

<customersTable>
   <customer>
         <firstName>Bob</firstname>
         <lastname>Jone</lastname>
   </customer>
   <customer>
          <firstName>James</firstName>
          <lastName>Smith</lastName>
   </customer>
</customersTable>

This is all I need, for a series of tables, I would want all fields in each table exported. It does not matter if all the tables are in the same file, but I guess it would be easier to deal with if I have each table in a separate file.

Thanks

I have looked at that link given by pgtips, that is a great page! I implemented one of the suggestions on there, and it does exactly what I wanted.
Excellent. Thanks.