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.