In the previous workshop we examined the usage of FOR XML AUTO. Today, we will examine FOR XML RAW, which is a bit more powerful when it comes to customizing the structure of XML output. Let us start with looking into the differences between the two.

To run the examples in this post, you need to create the tables by running the script given in the previous workshop.

Naming the elements

One of the visible differences between the two is that FOR XML AUTO will name the elements after the table name or alias name if it has one. On the contrary, FOR XML RAW allows you to specify the element name.

FOR XML AUTO

SELECT OrderNumber, CustomerID 
FROM OrderHeader
FOR XML AUTO
/*
<OrderHeader OrderNumber="SO101" CustomerID="1" />
<OrderHeader OrderNumber="SO102" CustomerID="1" />
*/

FOR XML RAW

SELECT OrderNumber, CustomerID 
FROM OrderHeader
FOR XML RAW('Order')
/*
<Order OrderNumber="SO101" CustomerID="1" />
<Order OrderNumber="SO102" CustomerID="1" />
*/

Note that in the first example, FOR XML AUTO picked up the table name as the name of the element. In the second example, FOR XML RAW allowed us to specify an element name.

Queries with multiple tables

The diffences will become more visible if there are more than one table involved in the query. FOR XML AUTO will generate an element for each table present in the query and create an element hierarchy depending upon the order in which tables appear in the JOIN. FOR XML RAW will generate only one element per row produced by the query.

FOR XML AUTO

SELECT 
	OrderNumber, CustomerNumber
FROM OrderHeader o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
FOR XML AUTO 
/*
<o OrderNumber="SO101">
  <c CustomerNumber="J001" />
</o>
<o OrderNumber="SO102">
  <c CustomerNumber="J001" />
</o>
*/

FOR XML RAW

SELECT 
	OrderNumber, CustomerNumber
FROM OrderHeader o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
FOR XML RAW('Order')
/*
<Order OrderNumber="SO101" CustomerNumber="J001" />
<Order OrderNumber="SO102" CustomerNumber="J001" />
*/

Generating a ROOT element

Just like FOR XML AUTO, FOR XML RAW also supports the ROOT directive that can be used to generate a root element.

SELECT OrderNumber, CustomerID 
FROM OrderHeader
FOR XML RAW('Order'), ROOT('Orders')
/*
<Orders>
  <Order OrderNumber="SO101" CustomerID="1" />
  <Order OrderNumber="SO102" CustomerID="1" />
</Orders>
*/

Generating Column Values as elements

If you would like to generate column values as elements instead of attributes, you can do so by specifying the ELEMENTS directive.

SELECT OrderNumber, CustomerID 
FROM OrderHeader
FOR XML RAW('Order'), ELEMENTS, ROOT('Orders')
/*
<Orders>
  <Order>
    <OrderNumber>SO101</OrderNumber>
    <CustomerID>1</CustomerID>
  </Order>
  <Order>
    <OrderNumber>SO102</OrderNumber>
    <CustomerID>1</CustomerID>
  </Order>
</Orders>
*/

Just like FOR XML AUTO, FOR XML RAW too is relatively very easy to use. It gives a little bit more control over the structure of the output. Other directives (PATH and EXPLICIT) offers much more control over the output and allows you to better customize the shape of the XML output. We will see more examples of PATH and EXPLICIT in the future posts.