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.
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
The EmpLastName assignment shows how you can assign your own custom XML tags based on each column.
