Retrieving Resultset Using XML

We can generate XML documents by using SQL Server FOR XML. There are 4 methods we can include with FOR XML are AUTO, EXPLICIT, PATH, RAW.

FOR XML AUTO - Displays nested XML tree with each column representing as a single element.

Sample Data

Examples

SELECT * FROM TestIdentity WHERE id = 1 FOR XML AUTO <TestIdentity Id="1" NAME="Kalyan" Phone="9090909092" City="Hyderabad" />

SELECT * FROM TestIdentity AS Students WHERE id = 1 FOR XML AUTO  <Students Id="1" NAME="Kalyan" Phone="9090909092" City="Hyderabad" />
SELECT
* FROM TestIdentity AS Students WHERE id in (1,2) FOR XML AUTO,ELEMENTS
<Students><
Id>1</Id><
NAME>Kalyan</NAME><
Phone>9090909092</Phone><
City>Hyderabad</City></
Students><
Students><
Id>2</Id><
NAME>Kishore</NAME><
Phone>8080808082</Phone><
City>Secunderabad</City></
Students>
SELECT * FROM TestIdentity WHERE id IN(1,2) FOR XML AUTO, ROOT('students')
<students><
Students Id="1" NAME="Kalyan" Phone="9090909092" City="Hyderabad" /><
Students Id="2" NAME="Kishore" Phone="8080808082" City="Secunderabad" /></
students>
SELECT CASE WHEN LEN(NAMES)>0 THEN LEFT(NAMES, LEN(NAMES)-1) ELSE '' END AS NAMES_CSV
FROM (SELECT T.NAME + ',' FROM TestIdentity T FOR XML PATH('')) TMP_NAMES (NAMES)
Kalyan,Kishore,Krishna,Raju,Ramesh,Shiva,Mallik,Naveen,Madhu,Srinu,Harish,Ramkumar

Using the below query we can create as csv

SELECT City, STUFF((SELECT ',' + T.NAME FROM TestIdentity T WHERE T.City = Ti.City FOR XML PATH('')),1,1,'')
FROM TestIdentity Ti GROUP BY City