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.