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

I've got a simple table and want to store its content 开发者_如何学Gointo 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

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....

Marc

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_gci1265579,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.

SELECT CAST('1' AS XML)

This Query fire in sql and your copy data put inside then show XML Result.