Transform Each Row Into XML

This neat and simple little trick will help to transform each row in a table or query into an XML row. The simplest way is to use the FOR XML clause. However if you want to return the XML as a separate column in the table, you need to perform a self-join on the table itself.

[cc lang=”sql”]
SELECT TOP 100
EmployeeID,
EmployeeXML =
(
SELECT
EmpLastName = LastName
,e_xml.*
FROM dbo.Employee e_xml
WHERE e_xml.EmployeeID = emp.EmployeeID
FOR XML PATH (”)
)
FROM dbo.Employee emp
[/cc]

The EmpLastName assignment shows how you can assign your own custom XML tags based on each column.

3 comments
Krish 09 Jul 2014 at 8:32 am

Hey,

i used this logic. its working fine. great ….!
thanks a lot

Wiedzma 03 Aug 2015 at 3:36 pm

select (select K.* for xml raw) from sys.objects K

Rupesh S 12 Jul 2016 at 11:59 am

thanks a lot….

Featured Articles

 Site Author

  • Thanks for visiting!