Updated: Formatted XML From SQL

In my previous post I showed you how to return XML from the SQL Server Database. To further this topic, you can also format your XML to use Xml elements. This time, we’re going to take a list of Customers and view their orders along with some information from the order, thats right, its straight from MSDN (with a couple tweaks).

This SQL Query will return Xml formatted as formatted elements.

USE AdventureWorks;
GO

SELECT TOP 10
     Customer.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Customer.CustomerType
FROM Sales.Customer Customer, Sales.SalesOrderHeader OrderHeader
WHERE Customer.CustomerID = OrderHeader.CustomerID
ORDER BY Customer.CustomerID
FOR XML AUTO, ELEMENTS, ROOT(‘Customers’)

Returns:

<Customers>
   <Customer>
      <CustomerID>1</CustomerID>
      <CustomerType>S</CustomerType>
         <OrderHeader>
            <CustomerID>1</CustomerID>
            <SalesOrderID>43860</SalesOrderID>
            <Status>5</Status>
         </OrderHeader>
         <OrderHeader>
            <CustomerID>1</CustomerID>
            <SalesOrderID>44501</SalesOrderID>
            <Status>5</Status>
         </OrderHeader>
         <OrderHeader>
            <CustomerID>1</CustomerID>
            <SalesOrderID>45283</SalesOrderID>
            <Status>5</Status>
         </OrderHeader>
         <OrderHeader>
            <CustomerID>1</CustomerID>
            <SalesOrderID>46042</SalesOrderID>
            <Status>5</Status>
         </OrderHeader>
      </Customer>
      …
<Customers>

 

Explanation

The XML AUTO mode tells SQL Server to return the data in a XML format. We have provided the Sales.Customer table an alias of “Customer”. If I had not done this, the results would have printed each element as:

<Sales.Customer>…</Sales.Customer>

This is not desirable.

The ELEMENTS option tells SQL Server to return the data as Xml elements, not as attributes. Had we eliminated this from the query, our query would have returned this:

<Customers>
   <Customer CustomerID=”1″ CustomerType=”S”>
      <OrderHeader CustomerID=”1″ SalesOrderID=”43860″ Status=”5″ />
      <OrderHeader CustomerID=”1″ SalesOrderID=”44501″ Status=”5″ />
      …
   …
</Customers>

This can work, but this time I wanted to demostrate the ELEMENT option.

The ROOT(‘Customers’) allows us to set a root Xml element named “Customers”.

 

Conclusion

While this may not be the next best thing you’ve ever seen, its still cool and it can save you time in the long run. Possible uses might include a quick way to get data out of your system and into a XML format for a one time use. Or you could use it for your data access layer and have it return XML to the business layer that might be used in a web service.

Its been reported that working with large XML Docs in .NET can be memory intensive, so perhaps this might help in processing and in saving memory on that server thats running on an old desktop that your boss had laying around. (We’ve all worked at a company who had a ‘server’ which was nothing more than a beefy desktop sitting in the server room.) 🙂