SQL Server 2000 introduced an enhancement to the
T-SQL syntax that enables normal relational queries to output their
result set as XML, using any of these three approaches:
FOR XML RAW
FOR XML AUTO
FOR XML EXPLICIT
As you probably
expected, these three features are also in SQL Server 2005. We’ll first
discuss the features common to both versions and then look closely at
the new and enhanced features available in SQL Server 2005.
FOR XML RAW
FOR XML RAW produces what we call attribute-based XML. FOR XML RAW
essentially creates a flat representation of the data where each row
returned becomes an element and the returned columns become the
attributes of each element. FOR XML RAW also doesn’t interpret joins in any special way. (Joins become relevant in FOR XML AUTO.) Listing 1 is an example of a simple query that retrieves customer and order header data.
Listing 1. Customer and Order Header Data with FOR XML RAW
USE AdventureWorks
GO
SELECT TOP 10 -- limits the result rows for demo purposes
Customer.CustomerID, OrderHeader.SalesOrderID, OrderHeader.OrderDate
FROM Sales.Customer Customer
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON OrderHeader.CustomerID = Customer.CustomerID
ORDER BY Customer.CustomerID
FOR XML RAW
|
If you are using SQL
Server 2000, this will be output as a stream of text to Query Analyzer.
However, we will assume that you are working with SQL Server 2005, so
you can click on the XML hyperlink in the returned results to see the
results shown here:
<row CustomerID="1" SalesOrderID="43860" OrderDate="2001-08-01T00:00:00" />
<row CustomerID="1" SalesOrderID="44501" OrderDate="2001-11-01T00:00:00" />
<row CustomerID="1" SalesOrderID="45283" OrderDate="2002-02-01T00:00:00" />
<row CustomerID="1" SalesOrderID="46042" OrderDate="2002-05-01T00:00:00" />
<row CustomerID="2" SalesOrderID="46976" OrderDate="2002-08-01T00:00:00" />
<row CustomerID="2" SalesOrderID="47997" OrderDate="2002-11-01T00:00:00" />
<row CustomerID="2" SalesOrderID="49054" OrderDate="2003-02-01T00:00:00" />
<row CustomerID="2" SalesOrderID="50216" OrderDate="2003-05-01T00:00:00" />
<row CustomerID="2" SalesOrderID="51728" OrderDate="2003-08-01T00:00:00" />
<row CustomerID="2" SalesOrderID="57044" OrderDate="2003-11-01T00:00:00" />
As promised, you get flat results where each row returned from the query becomes a single element named row
and where all columns are output as attributes of that element. Odds
are, however, that you will want more structured XML output, which leads
us to the next topic, FOR XML AUTO.
FOR XML AUTO
FOR XML AUTO
produces attribute-based XML by default and can create nested results
based on the tables in the query’s join clause. For example, using the
same query just demonstrated, you can simply change the FOR XML clause to FOR XML AUTO, as shown here.
Listing 2. Customer and Order Header Data with FOR XML AUTO
USE AdventureWorks
GO
SELECT TOP 10 -- limits the result rows for demo purposes
Customer.CustomerID, OrderHeader.SalesOrderID, OrderHeader.OrderDate
FROM Sales.Customer Customer
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON OrderHeader.CustomerID = Customer.CustomerID
ORDER BY Customer.CustomerID
FOR XML AUTO
|
Execute and click the XML hyperlink in the results, and you will see the following output:
<Customer CustomerID="1">
<OrderHeader SalesOrderID="43860" OrderDate="2001-08-01T00:00:00" />
<OrderHeader SalesOrderID="44501" OrderDate="2001-11-01T00:00:00" />
<OrderHeader SalesOrderID="45283" OrderDate="2002-02-01T00:00:00" />
<OrderHeader SalesOrderID="46042" OrderDate="2002-05-01T00:00:00" />
</Customer>
<Customer CustomerID="2">
<OrderHeader SalesOrderID="46976" OrderDate="2002-08-01T00:00:00" />
<OrderHeader SalesOrderID="47997" OrderDate="2002-11-01T00:00:00" />
<OrderHeader SalesOrderID="49054" OrderDate="2003-02-01T00:00:00" />
<OrderHeader SalesOrderID="50216" OrderDate="2003-05-01T00:00:00" />
<OrderHeader SalesOrderID="51728" OrderDate="2003-08-01T00:00:00" />
<OrderHeader SalesOrderID="57044" OrderDate="2003-11-01T00:00:00" />
</Customer>
As you can see, the XML data has main elements named Customer (based on the alias assigned in the query) and subelements named OrderHeader (again from the alias). Note that FOR XML AUTO determines the element nesting order based on the order of the columns in the SELECT clause. You can rewrite the SELECT clause so an OrderHeader column comes before a Customer Column, as shown here:
SELECT TOP 10 -- limits the result rows for demo purposes
OrderHeader.SalesOrderID, OrderHeader.OrderDate, Customer.CustomerID
The output (as viewed in the XML viewer) now looks like this:
<OrderHeader SalesOrderID="43860" OrderDate="2001-08-01T00:00:00">
<Customer CustomerID="1" />
</OrderHeader>
<OrderHeader SalesOrderID="44501" OrderDate="2001-11-01T00:00:00">
<Customer CustomerID="1" />
</OrderHeader>
<OrderHeader SalesOrderID="45283" OrderDate="2002-02-01T00:00:00">
<Customer CustomerID="1" />
</OrderHeader>
<OrderHeader SalesOrderID="46042" OrderDate="2002-05-01T00:00:00">
<Customer CustomerID="1" />
</OrderHeader>
<OrderHeader SalesOrderID="46976" OrderDate="2002-08-01T00:00:00">
<Customer CustomerID="2" />
</OrderHeader>
<OrderHeader SalesOrderID="47997" OrderDate="2002-11-01T00:00:00">
<Customer CustomerID="2" />
</OrderHeader>
<OrderHeader SalesOrderID="49054" OrderDate="2003-02-01T00:00:00">
<Customer CustomerID="2" />
</OrderHeader>
<OrderHeader SalesOrderID="50216" OrderDate="2003-05-01T00:00:00">
<Customer CustomerID="2" />
</OrderHeader>
<OrderHeader SalesOrderID="51728" OrderDate="2003-08-01T00:00:00">
<Customer CustomerID="2" />
</OrderHeader>
<OrderHeader SalesOrderID="57044" OrderDate="2003-11-01T00:00:00">
<Customer CustomerID="2" />
</OrderHeader>
These
results are probably not the results you wanted. To keep the XML
hierarchy matching the table hierarchy, you must list at least one
column from the parent table before any column from a child table. If
there are three levels of tables, at least one other column from the
child table must come before any from the grandchild table, and so on.
