An XML document can be generated from the result of a SQL Server relational query using FOR XML. FOR XML can be used along with AUTO, RAW, PATH or EXPLICIT directives which offer different levels of control over the structure of the XML output.
Out of the four directives that can be used with FOR XML, AUTO seems to be the easiest one. When using AUTO mode, SQL Server AUTO-matically (as the name suggests) shapes the XML document based on the way the query is constructed. With AUTO, you have very little control over the structure of the XML output, but it requires very little input from you.
In this workshop, let us see a few examples that generate XML documents with different structure and shape. Before we get started, we need some sample data. Run the following script to generate the sample data needed for this lab.
Sample Data
IF OBJECT_ID('Customers','U') IS NOT NULL BEGIN
DROP TABLE Customers
END
CREATE TABLE Customers (
CustomerID INT IDENTITY PRIMARY KEY,
CustomerNumber VARCHAR(20),
Name VARCHAR(40),
Phone VARCHAR(15),
Street VARCHAR(40),
City VARCHAR(20),
State VARCHAR(10),
Zip VARCHAR(10) )
GO
IF OBJECT_ID('Items','U') IS NOT NULL BEGIN
DROP TABLE Items
END
CREATE TABLE Items (
ItemID INT IDENTITY PRIMARY KEY,
ItemNumber VARCHAR(20),
ItemDescription VARCHAR(40) )
GO
IF OBJECT_ID('OrderHeader','U') IS NOT NULL BEGIN
DROP TABLE OrderHeader
END
CREATE TABLE OrderHeader (
OrderID INT IDENTITY PRIMARY KEY,
OrderNumber VARCHAR(20),
OrderDate DATETIME,
CustomerID INT )
GO
IF OBJECT_ID('OrderDetails','U') IS NOT NULL BEGIN
DROP TABLE OrderDetails
END
CREATE TABLE OrderDetails (
OrderDetailID INT IDENTITY PRIMARY KEY,
OrderID INT,
ItemID INT,
Quantity INT,
Price MONEY )
-- Populate Customer Table
INSERT INTO Customers (
CustomerNumber, Name, Phone, Street,
City, State, Zip )
SELECT
'J001', 'Jacob Sebastian', '(999) 999-9999',
'401, Jacobs Street', 'New York', 'NY', '12345'
-- Populate Items Table
INSERT INTO Items (ItemNumber, ItemDescription)
SELECT 'D001','DELL XPS 1130 Laptop'
UNION ALL
SELECT 'Z001','XBOX 360 Console'
-- Create order "SO101"
INSERT INTO OrderHeader( OrderNumber, OrderDate, CustomerID )
SELECT 'SO101','2009-01-23',1
-- Add Line Items
INSERT INTO OrderDetails (OrderID, ItemID, Quantity, Price)
SELECT 1, 1, 1, 900
UNION ALL
SELECT 1, 2, 1, 200
-- Create order "SO102"
INSERT INTO OrderHeader( OrderNumber, OrderDate, CustomerID )
SELECT 'SO102','2009-01-24',1
-- Add Line Items
INSERT INTO OrderDetails (OrderID, ItemID, Quantity, Price)
SELECT 2, 1, 1, 900
Getting Started
The simplest version of FOR XML AUTO query can be written by just appending “FOR XML AUTO” at the end of a normal relational query. Here is an example:
SELECT OrderNumber, CustomerID
FROM OrderHeader
FOR XML AUTO
/*
<OrderHeader OrderNumber="SO101" CustomerID="1" />
<OrderHeader OrderNumber="SO102" CustomerID="1" />
*/
When using FOR XML with AUTO, SQL Server looks into the way the query is structured and generates an XML output based on that. In the above example, an XML element is created for each row in the OrderHeader table. The top level elements are named after the table name or alias.
In case you want to generate the top level elements with a different name, you can achieve it by adding a table alias. If the table has an alias, SQL Server will generate the top level elements using the alias name. Here is an example that generates the top level elements with "o" instead of "OrderHeader".
SELECT OrderNumber, CustomerID
FROM OrderHeader o
FOR XML AUTO
/*
<o OrderNumber="SO101" CustomerID="1" />
<o OrderNumber="SO102" CustomerID="1" />
*/
The XML we just generated is not really a well-formed XML document, but just an XML fragment. An XML document should have a single root element. Most of the times you need to generate well-formed XML documents instead of just XML fragments. A root element can be added to the XML document by specifying the ROOT() attribute.
SELECT OrderNumber, CustomerID
FROM OrderHeader
FOR XML AUTO, ROOT('SalesOrder')
/*
<SalesOrder>
<OrderHeader OrderNumber="SO101" CustomerID="1" />
<OrderHeader OrderNumber="SO102" CustomerID="1" />
</SalesOrder>
*/
If the query contains more than one table, SQL Server will generate a hierarchy of XML nodes based on the order in which tables are joined in the query. If the order of the tables changes, the structure of the XML output will also change. The following example shows a query with two tables and shows how SQL Server shapes the structure of the output XML.
SELECT
[Order].OrderNumber, [Order].OrderDate,
Customer.CustomerNumber, Customer.Name
FROM OrderHeader [Order]
INNER JOIN Customers Customer ON
[Order].CustomerID = Customer.CustomerID
AND OrderNumber = 'SO101'
FOR XML AUTO
/*
<Order OrderNumber="SO101" OrderDate="2009-01-23T00:00:00">
<Customer CustomerNumber="J001" Name="Jacob Sebastian" />
</Order>
*/
If there are more tables in the query, the XML hierarchy will be deeper. Here is another example with three tables. Note that SQL Server puts the data of the first table in the top most element and the next table within the top level element. A third level child element is created for the data of the third table used in the query.
SELECT
[Order].OrderNumber, [Order].OrderDate,
Customer.CustomerNumber, Customer.Name,
details.ItemID,
details.Quantity,
details.Price
FROM OrderHeader [Order]
INNER JOIN Customers Customer ON [Order].CustomerID = Customer.CustomerID
AND OrderNumber = 'SO101'
INNER JOIN dbo.OrderDetails Details ON Details.OrderID = [order].orderID
FOR XML AUTO
/*
<Order OrderNumber="SO101" OrderDate="2009-01-23T00:00:00">
<Customer CustomerNumber="J001" Name="Jacob Sebastian">
<Details ItemID="1" Quantity="1" Price="900.0000" />
<Details ItemID="2" Quantity="1" Price="200.0000" />
</Customer>
</Order>
*/
Let us continue our experiment and add one more table to the query. As you would expect, one more level will be added in the hierarchy of the XML output.
SELECT
[Order].OrderNumber, [Order].OrderDate,
Customer.CustomerNumber, Customer.Name,
details.ItemID,
details.Quantity,
details.Price,
item.ItemNumber,
item.ItemDescription
FROM OrderHeader [Order]
INNER JOIN Customers Customer ON [Order].CustomerID = Customer.CustomerID
AND OrderNumber = 'SO101'
INNER JOIN dbo.OrderDetails Details ON Details.OrderID = [order].orderID
INNER JOIN dbo.Items item ON item.ItemID = details.ItemID
FOR XML AUTO
/*
<Order OrderNumber="SO101" OrderDate="2009-01-23T00:00:00">
<Customer CustomerNumber="J001" Name="Jacob Sebastian">
<Details ItemID="1" Quantity="1" Price="900.0000">
<item ItemNumber="D001" ItemDescription="DELL XPS 1130 Laptop" />
</Details>
<Details ItemID="2" Quantity="1" Price="200.0000">
<item ItemNumber="Z001" ItemDescription="XBOX 360 Console" />
</Details>
</Customer>
</Order>
*/
This may not be the way you might always want the output to be. If you would like to control the depth of the XML tree, you can do so by rewriting your query. SQL Server looks at the outer most query to decide the structure of the XML output. So you can move some of the tables to inner queries to hide them from the XML generator. The following code snippet shows an example:
SELECT
[Order].OrderNumber, [Order].OrderDate,
Customer.CustomerNumber, Customer.Name,
details.ItemID,
details.Quantity,
details.Price,
details.ItemNumber,
details.ItemDescription
FROM OrderHeader [Order]
INNER JOIN Customers Customer ON [Order].CustomerID = Customer.CustomerID
AND OrderNumber = 'SO101'
INNER JOIN (
SELECT
d.OrderID,
d.ItemID,
d.Quantity,
d.Price,
i.ItemNumber,
i.itemdescription
FROM dbo.OrderDetails d
INNER JOIN items i ON i.itemid = d.itemid
) details ON details.orderid = [order].OrderID
FOR XML AUTO
/*
<Order OrderNumber="SO101" OrderDate="2009-01-23T00:00:00">
<Customer CustomerNumber="J001" Name="Jacob Sebastian">
<details ItemID="1" Quantity="1" Price="900.0000"
ItemNumber="D001" ItemDescription="DELL XPS 1130 Laptop" />
<details ItemID="2" Quantity="1" Price="200.0000"
ItemNumber="Z001" ItemDescription="XBOX 360 Console" />
</Customer>
</Order>
*/
By default, SQL Server generates columns values as attributes when using FOR XML AUTO. You can turn them into elements by specifying the ELEMENTS directive along with your query.
SELECT
[Order].OrderNumber, [Order].OrderDate,
Customer.CustomerNumber, Customer.Name
FROM OrderHeader [Order]
INNER JOIN Customers Customer ON [Order].CustomerID = Customer.CustomerID
AND OrderNumber = 'SO101'
FOR XML AUTO, ELEMENTS
/*
<Order>
<OrderNumber>SO101</OrderNumber>
<OrderDate>2009-01-23T00:00:00</OrderDate>
<Customer>
<CustomerNumber>J001</CustomerNumber>
<Name>Jacob Sebastian</Name>
</Customer>
</Order>
*/
FOR XML AUTO is relatively very easy to use, but offers very little control over the strucutre of the XML document being generated. Other directives (RAW, PATH and EXPLICIT) offers more control over the output and allows you to better customize the shape of the XML output. We will see more examples of RAW, PATH and EXPLICIT in the future posts.