SQL to XML

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:

<ORDERS> 
    <OrderID>10258</OrderID> 
    <CustomerID>ERNSH</CustomerID> 
    <EmployeeID>1</EmployeeID> 
    <OrderDate>1996-07-17T00:00:00</OrderDate> 
    <RequiredDate>1996-08-14T00:00:00</RequiredDate> 
    <ShippedDate>1996-07-23T00:00:00</ShippedDate> 
    <ShipVia>1</ShipVia> 
    <Freight>140.5100</Freight> 
    <ShipName>Ernst Handel</ShipName> 
    <ShipAddress>Kirchgasse 6</ShipAddress> 
    <ShipCity>Graz</ShipCity> 
    <ShipPostalCode>8010</ShipPostalCode> 
    <ShipCountry>Austria</ShipCountry> 
    <CUSTOMERS> 
        <CustomerID>ERNSH</CustomerID> 
        <CompanyName>Ernst Handel</CompanyName> 
        <ContactName>Roland Mendel</ContactName> 
        <ContactTitle>Sales Manager</ContactTitle> 
        <Address>Kirchgasse 6</Address> 
        <City>Graz</City> 
        <PostalCode>8010</PostalCode> 
        <Country>Austria</Country> 
        <Phone>7675-3425</Phone> 
        <Fax>7675-3426</Fax> 
        <EMPLOYEES> 
            <EmployeeID>1</EmployeeID> 
            <LastName>Davolio</LastName> 
            <FirstName>Nancy</FirstName> 
            <Title>Sales Representative</Title> 
            <TitleOfCourtesy>Ms.</TitleOfCourtesy> 
            <BirthDate>1948-12-08T00:00:00</BirthDate> 
            <HireDate>1992-05-01T00:00:00</HireDate> 
            <Address> 507 - 20th Ave. E. Apt. 2A </Address> 
            <City>Seattle</City> 
            <Region>WA</Region> 
            <PostalCode>98122</PostalCode> 
            <Country>USA</Country> 
            <HomePhone>(206) 555-9857</HomePhone> 
            <Extension>5467</Extension> 
            <Photo>dbobject/EMPLOYEES[@EmployeeID='1']/@Photo</Photo> 
            <Notes>Education includes a BA in psychology from Colorado...</Notes>
            <ReportsTo>2</ReportsTo> 
            <PhotoPath>http://accweb/emmployees/davolio.bmp</PhotoPath> 
        </EMPLOYEES> 
    </CUSTOMERS> 
</ORDERS>

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