Today I went through the sql statements for XML. Here I am sharing the same as a reference
Table to XML
----------------
Creating XML from a TABLE using SELECT statement is very simple in SQL Server 2008. We can form two types of XML from T-SQL statements
1. Each row as element and column values as attributes
SQL
use Northwind
GO
select * from Customers as customer where CustomerID in ('ALFKI','ANATR')
for xml auto, root('customers')
GO
RESULT
<customers>
<customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545" />
<customer CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados" ContactName="Ana Trujillo" ContactTitle="Owner" Address="Avda. de la Constitución 2222" City="México D.F." PostalCode="05021" Country="Mexico" Phone="(5) 555-4729" Fax="(5) 555-3745" />
</customers>
2. Each row as parent node and column values as child nodes
SQL
use Northwind
GO
select * from Customers where CustomerID in ('ALFKI','ANATR')
for xml path('customer') ,root('customers')
GO
RESULT
<customers>
<customer>
<CustomerID>ALFKI</CustomerID>
<CompanyName>Alfreds Futterkiste</CompanyName>
<ContactName>Maria Anders</ContactName>
<ContactTitle>Sales Representative</ContactTitle>
<Address>Obere Str. 57</Address>
<City>Berlin</City>
<PostalCode>12209</PostalCode>
<Country>Germany</Country>
<Phone>030-0074321</Phone>
<Fax>030-0076545</Fax>
</customer>
<customer>
<CustomerID>ANATR</CustomerID>
<CompanyName>Ana Trujillo Emparedados y helados</CompanyName>
<ContactName>Ana Trujillo</ContactName>
<ContactTitle>Owner</ContactTitle>
<Address>Avda. de la Constitución 2222</Address>
<City>México D.F.</City>
<PostalCode>05021</PostalCode>
<Country>Mexico</Country>
<Phone>(5) 555-4729</Phone>
<Fax>(5) 555-3745</Fax>
</customer>
</customers>
In the above sql, if we does not specify the path name - 'customer' in this example,
SQL server will return <row> as the parent element. The SQL statement will go like this
select * from Customers where CustomerID in ('ALFKI','ANATR')
for xml path ,root('customers')
XML to Table
--------------
In order to convert XML data to Table records we have to get store the XML data in a variable
1. Each row as element and column values as attributes
SQL
use Northwind
GO
declare @xml as xml
set @xml= (select CustomerID,CompanyName,ContactName,ContactTitle,Address,City
from Customers as customer
where CustomerID in ('ALFKI','ANATR')
for xml auto, root('customers'))
SELECT
Tbl.Col.value('@CustomerID', 'nchar(10)') CUSTID,
Tbl.Col.value('@CompanyName', 'nvarchar(80)') COMPNAME,
Tbl.Col.value('@ContactName', 'nvarchar(60)') CONTNAME,
Tbl.Col.value('@ContactTitle','nvarchar(60)') CONTTITLE,
Tbl.Col.value('@Address', 'nvarchar(120)') ADDRESS,
Tbl.Col.value('@City', 'nvarchar(30)') CITY
FROM @xml.nodes('//customers/customer') Tbl(Col)
GO
RESULT
CUSTID COMPNAME CONTNAME CONTTITLE ADDRESS CITY
-----------------------------------------------------------------------------------------------------------------------------------
ALFKI Alfreds Futterkiste Maria Anders Sales Representative Obere Str. 57 Berlin
ANATR Ana Trujillo Emparedados y helados Ana Trujillo Owner Avda. de la Constitución 2222 México D.F.
2. Each row as parent node and column values as child nodes
SQL
use Northwind
GO
declare @xml as xml
set @xml= (select CustomerID,CompanyName,ContactName,ContactTitle,Address,City
from Customers
where CustomerID in ('ALFKI','ANATR')
for xml path('customer'), root('customers'))
SELECT
Tbl.Col.value('CustomerID[1]', 'nchar(10)') CUSTID,
Tbl.Col.value('CompanyName[1]', 'nvarchar(80)') COMPNAME,
Tbl.Col.value('ContactName[1]', 'nvarchar(60)') CONTNAME,
Tbl.Col.value('ContactTitle[1]','nvarchar(60)') CONTTITLE,
Tbl.Col.value('Address[1]', 'nvarchar(120)') ADDRESS,
Tbl.Col.value('City[1]', 'nvarchar(30)') CITY
FROM @xml.nodes('//customers/customer') Tbl(Col)
GO
RESULT
CUSTID COMPNAME CONTNAME CONTTITLE ADDRESS CITY
-----------------------------------------------------------------------------------------------------------------------------------
ALFKI Alfreds Futterkiste Maria Anders Sales Representative Obere Str. 57 Berlin
ANATR Ana Trujillo Emparedados y helados Ana Trujillo Owner Avda. de la Constitución 2222 México D.F.
COLUMN VALUES AS COMMA SEPARATED STRING
----------------------------------------------------------------
One of the interesting thing in SQL for XML is with single line of code we can convert all the values in a table column to a comma separated string
SQL
use Northwind
GO
select stuff((select ','+ customerid from Customers for xml path('')),1,1,'') as columnvalues
GO
RESULT
ALFKI,ANATR,ANTON,AROUT,BERGS,BLAUS,BLONP,BOLID,BONAP,BSBEV,CACTU,CENTC,CHOPS,CONSH,DRACD,DUMON,EASTC,ERNSH,FISSA,FOLIG,FOLKO,FRANK,FRANR,FRANS,FURIB,GALED,GODOS,KOENE,LACOR,LAMAI,LEHMS,MAGAA,MAISD,MORGK,NORTS,OCEAN,OTTIK,PARIS,PERIC,PICCO,PRINI,QUICK,RANCH,REGGC,RICSU,ROMEY,SANTG,SEVES,SIMOB,SPECD,SUPRD,TOMSP,TORTU,VAFFE,VICTE,VINET,WANDK,WARTH,WILMK,WOLZA,OLDWO,BOTTM,LAUGB,LETSS,HUNGO,GROSR,SAVEA,ISLAT,LILAS,THECR,RATTC,LINOD,GREAL,HUNGC,LONEP,THEBI,MEREP,HANAR,QUEDE,RICAR,COMMI,FAMIA,GOURL,QUEEN,TRADH,WELLI,HILAA,LAZYK,TRAIH,WHITC,SPLIR
Some more references
