Generate XML in SQL

You can easily generate Xml from a DataTable by using the .WriteXml() method.

But you can also bypass this and retrieve the Xml directly SQL Server 2005 by running the following command:

USE Northwind;

SELECT    EmployeeID,
        LastName,
        FirstName
FROM    dbo.Employees
FOR XML AUTO;

This will return an Xml representation of the data that looks like this:

<dbo.Employees EmployeeID=”1″ LastName=”Davolio” FirstName=”Nancy” />
<dbo.Employees EmployeeID=”2″ LastName=”Fuller” FirstName=”Andrew” />
<dbo.Employees EmployeeID=”3″ LastName=”Leverling” FirstName=”Janet” />
<dbo.Employees EmployeeID=”4″ LastName=”Peacock” FirstName=”Margaret” />
<dbo.Employees EmployeeID=”5″ LastName=”Buchanan” FirstName=”Steven” />
<dbo.Employees EmployeeID=”6″ LastName=”Suyama” FirstName=”Michael” />
<dbo.Employees EmployeeID=”7″ LastName=”King” FirstName=”Robert” />
<dbo.Employees EmployeeID=”8″ LastName=”Callahan” FirstName=”Laura” />
<dbo.Employees EmployeeID=”9″ LastName=”Dodsworth” FirstName=”Anne” />

For more information on FOR XML and its modes, click here.