What is the easiest way to export a SQLServer 2000 database to XML?

Question

I have an old SQL Server 2000 database that I want to get into XML so I can from there import parts of it into an SQLite database for a PHP website.

I've accessed the SQL Server 2000 database via SQL Server Management Studio 2008 Express, I can open all the tables, view the data, etc.

I was expecting to be able to e.g. right-click on "Tables" and select "Export all tables to XML" but cannot find any export feature like this.

What is the easiest way to export this SQL Server database to XML files, I don't even need the schema (int, varchar, etc.), just the data.

Answer 1

SELECT * FROM {tablename} FOR XML AUTO

is working fine in this particular case, thanks ogiboho via twitter

Answer 2

You could write a .NET app that retrieves the tables to a dataset and then get the XML from the dataset...

http://msdn.microsoft.com/en-us/library/zx8h06sz.aspx

Alternatively you could look at this forum post, it has lots of different ways to approach achieving this...

http://sqlxml.org/faqs.aspx?faq=29