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.
EmpLastName = LastName
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.