A statement like this (based on the infamous Northwind database):
SELECT
[EmployeeID] AS '@ID',
[LastName], [FirstName],
[Title],
[BirthDate], [HireDate]
FROM
[dbo].[Employees]
FOR XML PATH('Employee'), ROOT('Employees')
Output like this:
<Employees>
<Employee ID="1">
<LastName>Davolio</LastName>
<FirstName>Nancy</FirstName>
<Title>Sales Representative</Title>
<BirthDate>1948-12-08T00:00:00</BirthDate>
<HireDate>1992-05-01T00:00:00</HireDate>
</Employee>
<Employee ID="2">
<LastName>Fuller</LastName>
<FirstName>Andrew</FirstName>
<Title>Vice President, Sales</Title>
<BirthDate>1952-02-19T00:00:00</BirthDate>
<HireDate>1992-08-14T00:00:00</HireDate>
</Employee>
Use the following links:
http://social.msdn.microsoft.com/Forums/pl/sqlxml/thread/5915dc22-562c-44ab-bd4d-041d43f47f1c[
^]
http://stackoverflow.com/questions/3952469/export-xml-from-sql-server[
^]
http://stackoverflow.com/questions/1564541/how-to-convert-records-in-a-table-to-xml-format-using-t-sql[
^]
Thanks,
Ambesha