I recently got asked if there was an easy way to take data from SQL Server and export it to XML. Well, turns out, the good people over at Microsoft made this VERY easy. Take your query that you want to export to XML, for our purposes, let's use:

SELECT TOP 1 * FROM ORDERS
INNER JOIN CUSTOMERS ON ORDERS.CUSTOMERID = CUSTOMERS.CUSTOMERID
INNER JOIN EMPLOYEES ON ORDERS.EMPLOYEEID = EMPLOYEES.EMPLOYEEID

and add FOR XML AUTO to the end of it. (oversimplification for some scenarios, but since this is a simple look at things, I am not going to dive into all that is possible. For that google FOR XML EXPLICIT. ) That will return you some Nicely formatted XML with each of the columns as an attribute and each row as an element. ... but say you didn't want that... ... say you wanted each column to be its own element. Then just add FOR XML AUTO, ELEMENTS onto the end of things.

SELECT TOP 1 * FROM ORDERS
INNER JOIN CUSTOMERS ON ORDERS.CUSTOMERID = CUSTOMERS.CUSTOMERID
INNER JOIN EMPLOYEES ON ORDERS.EMPLOYEEID = EMPLOYEES.EMPLOYEEID
FOR XML AUTO, ELEMENTS

This will render:

10258
ERNSH
1
1996-07-17T00:00:00
1996-08-14T00:00:00
1996-07-23T00:00:00
1
140.5100
Ernst Handel
Kirchgasse 6
Graz
8010
Austria

ERNSH
Ernst Handel
Roland Mendel
Sales Manager
Kirchgasse 6
Graz
8010
Austria
7675-3425
7675-3426

1
Davolio
Nancy

Ms.
1948-12-08T00:00:00
1992-05-01T00:00:00

507 - 20th Ave. E.
Apt. 2A

Seattle
WA
98122
USA
(206) 555-9857
5467
dbobject/EMPLOYEES[@EmployeeID='1']/@Photo
Education includes a BA in psychology from Colorado...
2
http://accweb/emmployees/davolio.bmp

A nice nested output Hope this helps some of you with your SQL XML needs.