FOR XML (SQL Server)

The estimated reading time for this post is 13 minutes

Sometimes it is required to return stored data in databases as XML documents. Usually, these requirements are relating to exchange data with other systems. By adding FOR XML to SELECT statement the result set will be returned as XML instead of rowset.

SQL Server provides four mode for the FOR XML clause:

  • RAW
  • AUTO
  • EXPLICIT
  • PATH

RAW Mode Queries

RAW mode is the simplest mode to work with. It generates a single <row> element per row.

SELECT C.BusinessEntityID AS CustomerID,
	ISNULL(C.Title+' ','')+
	ISNULL(C.FirstName+' ','')+
	ISNULL(C.MiddleName+' ','')+
	ISNULL(C.LastName+' ','') AS Customer,
	soh.SalesOrderID AS OrderID
FROM Person.Person AS C
INNER JOIN Sales.SalesOrderHeader AS soh
	ON soh.CustomerID = C.BusinessEntityID
	ORDER BY OrderDate DESC
	FOR XML RAW

 

The Result set

<row CustomerID="14501" Customer="Bryan J Gray " OrderID="43700" />
<row CustomerID="11003" Customer="Jenna W Young " OrderID="43701" />
<row CustomerID="16624" Customer="Nichole A Goel " OrderID="43703" />
<row CustomerID="11005" Customer="Joe Madan " OrderID="43704" />
<row CustomerID="11011" Customer="Joe D Rana " OrderID="43705" />

 

You can change the element name from row to any name that you want by specifying name after FOR XML RAW:

SELECT C.BusinessEntityID AS CustomerID,
	ISNULL(C.Title+' ','')+
	ISNULL(C.FirstName+' ','')+
	ISNULL(C.MiddleName+' ','')+
	ISNULL(C.LastName+' ','') AS Customer,
	soh.SalesOrderID AS OrderID
FROM Person.Person AS C
INNER JOIN Sales.SalesOrderHeader AS soh
	ON soh.CustomerID = C.BusinessEntityID
	ORDER BY OrderID
	FOR XML RAW ('Order')

 

The Result set

<Order CustomerID="14501" Customer="Bryan J Gray " OrderID="43700" />
<Order CustomerID="11003" Customer="Jenna W Young " OrderID="43701" />
<Order CustomerID="16624" Customer="Nichole A Goel " OrderID="43703" />
<Order CustomerID="11005" Customer="Joe Madan " OrderID="43704" />
<Order CustomerID="11011" Customer="Joe D Rana " OrderID="43705" />

 

As you see, the RAW clause does not generate a root for returned xml. You can also add root element by adding ROOT to the FOR XML clause:

SELECT C.BusinessEntityID AS CustomerID,
	ISNULL(C.Title+' ','')+
	ISNULL(C.FirstName+' ','')+
	ISNULL(C.MiddleName+' ','')+
	ISNULL(C.LastName+' ','') AS Customer,
	soh.SalesOrderID AS OrderID
FROM Person.Person AS C
INNER JOIN Sales.SalesOrderHeader AS soh
	ON soh.CustomerID = C.BusinessEntityID
	ORDER BY OrderID
	FOR XML RAW ('Order'), ROOT ('Orders')

 

The Result set

<Orders>
  <Order CustomerID="14501" Customer="Bryan J Gray " OrderID="43700" />
  <Order CustomerID="11003" Customer="Jenna W Young " OrderID="43701" />
  <Order CustomerID="16624" Customer="Nichole A Goel " OrderID="43703" />
  <Order CustomerID="11005" Customer="Joe Madan " OrderID="43704" />
  <Order CustomerID="11011" Customer="Joe D Rana " OrderID="43705" />
</Orders>

 

In addition, notice that in the previous examples the columns in the rowset are returned as attributes which called “attribute-centric” XML. You can modify your query to change this behavior to “element-centric” by adding ELEMENTS to the FOR XML clause:

SELECT C.BusinessEntityID AS CustomerID,
	ISNULL(C.Title+' ','')+
	ISNULL(C.FirstName+' ','')+
	ISNULL(C.MiddleName+' ','')+
	ISNULL(C.LastName+' ','') AS Customer,
	soh.SalesOrderID AS OrderID
FROM Person.Person AS C
INNER JOIN Sales.SalesOrderHeader AS soh
	ON soh.CustomerID = C.BusinessEntityID
	ORDER BY OrderID
	FOR XML RAW ('Order'), ROOT ('Orders'), ELEMENTS

 

The Result set

<Orders>
  <Order>
    <CustomerID>14501</CustomerID>
    <Customer>Bryan J Gray </Customer>
    <OrderID>43700</OrderID>
  </Order>
  <Order>
    <CustomerID>11003</CustomerID>
    <Customer>Jenna W Young </Customer>
    <OrderID>43701</OrderID>
  </Order>
  <Order>
    <CustomerID>16624</CustomerID>
    <Customer>Nichole A Goel </Customer>
    <OrderID>43703</OrderID>
  </Order>
  <Order>
    <CustomerID>11005</CustomerID>
    <Customer>Joe Madan </Customer>
    <OrderID>43704</OrderID>
  </Order>
  <Order>
    <CustomerID>11011</CustomerID>
    <Customer>Joe D Rana </Customer>
    <OrderID>43705</OrderID>
  </Order>
</Orders>

 

AUTO Mode Queries

The AUTO mode generates nested XML result based on the way the SELECT statement is specified. In this mode, you have minimal control over the shape of the generated XML. This mode is useful for simple hierarchy.

SELECT Customers.BusinessEntityID AS CustomerID,
	ISNULL(Customers.Title+' ','')+
	ISNULL(Customers.FirstName+' ','')+
	ISNULL(Customers.MiddleName+' ','')+
	ISNULL(Customers.LastName+' ','') AS Customer,
	Orders.SalesOrderID AS OrderID
FROM Person.Person AS Customers
INNER JOIN Sales.SalesOrderHeader AS Orders
	ON Orders.CustomerID = Customers.BusinessEntityID
	ORDER BY CustomerID
	FOR XML AUTO

 

The Result set

<Customers CustomerID="11000" Customer="Mary C Young ">
  <Orders OrderID="43793" />
  <Orders OrderID="51522" />
  <Orders OrderID="57418" />
</Customers>
<Customers CustomerID="11001" Customer="Amber L Young ">
  <Orders OrderID="43767" />
  <Orders OrderID="51493" />
</Customers>

 

Each table in the FROM clause with at least one listed column in the SELECT clause represent an XML element. The name of each table is using as the element name. For this reason, it is common to use alias for the table.

NULL Columns

In the XML result, NULL columns are omitted from the results by default. It can cause a specific problem when deriving XML schema from an XML document. For example, if someone sent you an XML document that contain product details, if none of the color of products was not specified, you would assume there is no column for color.

SELECT TOP 10 ProductID, Name, Color
FROM Production.Product AS Products
ORDER BY ProductID
FOR XML AUTO

The Result set

<Products ProductID="1" Name="Adjustable Race" />
<Products ProductID="2" Name="Bearing Ball" />
<Products ProductID="3" Name="BB Ball Bearing" />
<Products ProductID="4" Name="Headset Ball Bearings" />
<Products ProductID="316" Name="Blade" />
<Products ProductID="317" Name="LL Crankarm" Color="Black" />
<Products ProductID="318" Name="ML Crankarm" Color="Black" />
<Products ProductID="319" Name="HL Crankarm" Color="Black" />
<Products ProductID="320" Name="Chainring Bolts" Color="Silver" />
<Products ProductID="321" Name="Chainring Nut" Color="Silver" />

XSINIL

In situations where a schema needs to be derived from a document that contains nullable columns, SQL Server provides an option called XSINIL. By specifying the optional XSINIL parameter on the ELEMENTS option, you request an element for the NULL value. In this order, an element with xsi:nil attribute set to TRUE represents the NULL column value.

SELECT TOP 10 ProductID, Name, Color
FROM Production.Product AS Products
ORDER BY ProductID
FOR XML AUTO, ELEMENTS XSINIL

The Result set

<Products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductID>1</ProductID>
  <Name>Adjustable Race</Name>
  <Color xsi:nil="true" />
</Products>
<Products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductID>2</ProductID>
  <Name>Bearing Ball</Name>
  <Color xsi:nil="true" />
</Products>
<Products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductID>3</ProductID>
  <Name>BB Ball Bearing</Name>
  <Color xsi:nil="true" />
</Products>
<Products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductID>4</ProductID>
  <Name>Headset Ball Bearings</Name>
  <Color xsi:nil="true" />
</Products>
<Products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductID>316</ProductID>
  <Name>Blade</Name>
  <Color xsi:nil="true" />
</Products>
<Products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductID>317</ProductID>
  <Name>LL Crankarm</Name>
  <Color>Black</Color>
</Products>
<Products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductID>318</ProductID>
  <Name>ML Crankarm</Name>
  <Color>Black</Color>
</Products>
<Products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductID>319</ProductID>
  <Name>HL Crankarm</Name>
  <Color>Black</Color>
</Products>
<Products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductID>320</ProductID>
  <Name>Chainring Bolts</Name>
  <Color>Silver</Color>
</Products>
<Products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductID>321</ProductID>
  <Name>Chainring Nut</Name>
  <Color>Silver</Color>
</Products>

EXPLICIT Mode Queries

EXPLICIT mode gives you more control over the shape of the XML. EXPLICIT mode also provides more flexibility which you can mix elements and attributes on the way you like to shape the XML at the cost of greater complexity.

In order for EXPLICIT mode the rowset must have a specific format. the query must produce the following two metadata columns:

  • The first column provides the tag number of the current element, and the column must be named Tag. Each element must have a unique tag number.
  • The second column provides the tag number of parent element, and the column must be named Parent. NULL or 0 in the Parent column indicates that the corresponding element has no parent.

The following example retrieves orders for each customer:

SELECT
1 AS tag,
NULL AS Parent,
Customers.BusinessEntityID AS [Customer!1!CustomerID],
ISNULL(Customers.Title+' ','')+
ISNULL(Customers.FirstName+' ','')+
ISNULL(Customers.MiddleName+' ','')+
ISNULL(Customers.LastName+' ','') AS [Customer!1!CustomerName!ELEMENT],
NULL AS [ORDER!2!OrderID],
NULL AS [ORDER!2!OrderDate]
FROM Person.Person AS Customers
WHERE Customers.BusinessEntityID IN (20722,11712,20708)
UNION ALL
SELECT
2 AS tag,
1 AS Parent,
Customers.BusinessEntityID,
NULL,
Orders.SalesOrderID,
Orders.OrderDate
FROM Person.Person AS Customers
INNER JOIN Sales.SalesOrderHeader AS Orders
ON Orders.CustomerID = Customers.BusinessEntityID
WHERE Customers.BusinessEntityID IN (20722,11712,20708)
ORDER BY [Customer!1!CustomerID] DESC, [ORDER!2!OrderID]
FOR XML EXPLICIT

Note that for specifying column to be returned as element instead of attribute you need to add […!ELEMENT] to the end of column name.
 

The Result set

<Customer CustomerID="20722">
<CustomerName>Amy Liang </CustomerName>
<Order OrderID="48724" OrderDate="2006-12-31T00:00:00" />
<Order OrderID="51640" OrderDate="2007-07-29T00:00:00" />
<Order OrderID="58180" OrderDate="2007-11-18T00:00:00" />
</Customer>
<Customer CustomerID="20708">
<CustomerName>Amy Wu </CustomerName>
<Order OrderID="49037" OrderDate="2007-01-31T00:00:00" />
<Order OrderID="72551" OrderDate="2008-06-09T00:00:00" />
</Customer>
<Customer CustomerID="11712">
<CustomerName>Pedro Dominguez </CustomerName>
<Order OrderID="52231" OrderDate="2007-08-08T00:00:00" />
<Order OrderID="54118" OrderDate="2007-09-10T00:00:00" />
<Order OrderID="56245" OrderDate="2007-10-17T00:00:00" />
<Order OrderID="58619" OrderDate="2007-11-26T00:00:00" />
<Order OrderID="69595" OrderDate="2008-05-01T00:00:00" />
</Customer>

PATH Mode Queries

PATH mode provides a simpler way to mix elements and attributes. In PATH mode, Column names or column aliases are written as XML Path Language (XPath) expression.

SELECT Customers.BusinessEntityID AS '@CustomerID',
       ISNULL(Customers.Title+' ','')+
       ISNULL(Customers.FirstName+' ','')+
       ISNULL(Customers.MiddleName+' ','')+
       ISNULL(Customers.LastName+' ','') AS '@CustomernAME',
       Orders.SalesOrderID AS 'Order/OrderID',
       Orders.OrderDate AS 'Order/OrderDate'
FROM Person.Person AS Customers
INNER JOIN Sales.SalesOrderHeader AS Orders
ON Orders.CustomerID = Customers.BusinessEntityID
ORDER BY Orders.SalesOrderID
FOR XML PATH('Customer'), ROOT('Sales')

The Result set

<Sales>
  <Customer CustomerID="14501" CustomernAME="Bryan J Gray ">
    <Order>
      <OrderID>43700</OrderID>
      <OrderDate>2005-07-01T00:00:00</OrderDate>
    </Order>
  </Customer>
  <Customer CustomerID="11003" CustomernAME="Jenna W Young ">
    <Order>
      <OrderID>43701</OrderID>
      <OrderDate>2005-07-01T00:00:00</OrderDate>
    </Order>
  </Customer>
  <Customer CustomerID="16624" CustomernAME="Nichole A Goel ">
    <Order>
      <OrderID>43703</OrderID>
      <OrderDate>2005-07-02T00:00:00</OrderDate>
    </Order>
  </Customer>
  <Customer CustomerID="11005" CustomernAME="Joe Madan ">
    <Order>
      <OrderID>43704</OrderID>
      <OrderDate>2005-07-02T00:00:00</OrderDate>
    </Order>
  </Customer>
  <Customer CustomerID="11011" CustomernAME="Joe D Rana ">
    <Order>
      <OrderID>43705</OrderID>
      <OrderDate>2005-07-02T00:00:00</OrderDate>
    </Order>
  </Customer>
</Sales>

In this example note that the path to Customers.BusinessEntityID is shown as @CustomerID. Values that starts with an at sign (@) in XPath refer to attributes. The path to Orders.SalesOrderID is shown as Order/OrderID that indicates the Orders.SalesOrderID should be generated as an element named OrderID which is a sub-element of an element named Order.

PATH mode, together with the nesting of FOR XML queries and the TYPE clause, gives enough power to replace most of the EXPLICIT mode queries in a simpler, more maintainable way.

TYPE Keyword

The TYPE keyword returns FOR XML subqueries as xml data types rather than as nvarchar data types.

Take a look at the example below:

SELECT Customer.CustomerID,
              Customer.TerritoryID,
              (SELECT SalesOrderID,
                           [Status]
              FROM Sales.SalesOrderHeader AS soh
                     WHERE Customer.CustomerID = soh.CustomerID FOR XML AUTO
              ) AS Orders
FROM Sales.Customer AS Customer
       WHERE EXISTS(SELECT 1 FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID = Customer.CustomerID)
       ORDER BY Customer.CustomerID;

This query returns the Orders subquery as a varchar column rather than XML. Now look at the following example:

SELECT Customer.CustomerID,
              Customer.TerritoryID,
              (SELECT SalesOrderID,
                           [Status]
              FROM Sales.SalesOrderHeader AS soh
                     WHERE Customer.CustomerID = soh.CustomerID FOR XML AUTO, TYPE
              ) AS Orders
FROM Sales.Customer AS Customer
       WHERE EXISTS(SELECT 1 FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID = Customer.CustomerID)
       ORDER BY Customer.CustomerID;

By using the TYPE keyword, the subquery return data as an XML data type. By using this option with PATH mode, you can produce nested xml in simpler way than EXPLICIT.

SELECT Customer.CustomerID AS '@CustomerID',
              Customer.TerritoryID AS '@TerritoryID',
              (SELECT SalesOrderID,
                           [Status]
              FROM Sales.SalesOrderHeader AS soh
                     WHERE Customer.CustomerID = soh.CustomerID FOR XML AUTO, TYPE
              ) AS Orders
FROM Sales.Customer AS Customer
       WHERE EXISTS(SELECT 1 FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID = Customer.CustomerID)
       ORDER BY Customer.CustomerID
FOR XML PATH('Customer'), ROOT('Sales')

The Result Set

<Sales>
  <Customer CustomerID="11000" TerritoryID="9">
    <Orders>
      <soh SalesOrderID="43793" Status="5" />
      <soh SalesOrderID="51522" Status="5" />
      <soh SalesOrderID="57418" Status="5" />
    </Orders>
  </Customer>
  <Customer CustomerID="11001" TerritoryID="9">
    <Orders>
      <soh SalesOrderID="43767" Status="5" />
      <soh SalesOrderID="51493" Status="5" />
      <soh SalesOrderID="72773" Status="5" />
    </Orders>
  </Customer>
  <Customer CustomerID="11002" TerritoryID="9">
    <Orders>
      <soh SalesOrderID="43736" Status="5" />
      <soh SalesOrderID="51238" Status="5" />
      <soh SalesOrderID="53237" Status="5" />
    </Orders>
  </Customer>
</Sales>

This was our journey to FOR XML. Despite how basic itself might seem, it has a lot of options that give you enough ability to return the exact type of XML data you want. Hope this topic was informative, please stay tuned to learn more about SQL Server.

Hamide B. Abshuri

Hamide B. Abshuri is SQL Server Database Developer and Administrator with extensive of working experience. She started work with SQL Server since year 2015. She is currently Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer.

More Posts - Website


Leave a Comment

avatar
1000
  Subscribe  
Notify of