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

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