Transform Each Row Into XML
-
Posted on June 25, 2010 by Derek Dieter
-
0
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
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.
Post a comment
- Comments (RSS)
- Trackback
- Permalink
