How to convert records in a table to xml format using T-SQL?

Question

I've got a simple table and want to store its content into a xml on the harddrive. There should be one root element for the whole table, one element per table row and one child element per table column.

What possibilities do I have?

Thanks a lot Tomas

Answer 1

Use the FOR XML in your query.

E.g: - select * from table1 FOR XML AUTO

see this --> http://searchsqlserver.techtarget.com/tip/0,289483,sid87%5Fgci1265579,00.html

Alternatively, you can create your own XML in your t-sql code through cursors or in your application code, the longer way of doing it.

Answer 2

And if you need more control over how the resulting XML looks like, check out the new FOR XML PATH statement in SQL Server 2005 and newer.

A statement like this (based on the infamous Northwind database):

SELECT 
   CustomerID as "@CustomerID",
   CompanyName,
   Address as "address/street",
   City as "address/city",
   Region as "address/region",
   PostalCode as "address/zip",
   Country as "address/country",
   ContactName as "contact/name",
   ContactTitle as "contact/title",
   Phone as "contact/phone", 
   Fax as "contact/fax"
FROM Customers
FOR XML PATH('Customer')

will result in an output like this:

  <Customer CustomerID="ALFKI">
    <CompanyName>Alfreds Futterkiste</CompanyName>
    <address>
      <street>Obere Str. 57</street>
      <city>Berlin</city>
      <zip>12209</zip>
      <country>Germany</country>
    </address>
    <contact>
      <name>Maria Anders</name>
      <title>Sales Representative</title>
      <phone>030-0074321</phone>
      <fax>030-0076545</fax>
    </contact>
  </Customer>

That's rather tricky to get any other way....

See here for a good introduction to what FOR XML PATH can do.

Marc