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 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
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
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
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
14501
Bryan J Gray
43700
11003
Jenna W Young
43701
16624
Nichole A Goel
43703
11005
Joe Madan
43704
11011
Joe D Rana
43705
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
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, ColorFROM Production.Product AS ProductsORDER BY ProductIDFOR XML AUTO
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, ColorFROM Production.Product AS ProductsORDER BY ProductIDFOR XML AUTO, ELEMENTS XSINIL
1 Adjustable Race 2 Bearing Ball 3 BB Ball Bearing 4 Headset Ball Bearings 316 Blade 317 LL Crankarm Black 318 ML Crankarm Black 319 HL Crankarm Black 320 Chainring Bolts Silver 321 Chainring Nut Silver
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:SELECT1 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 CustomersWHERE Customers.BusinessEntityID IN (20722,11712,20708)UNION ALLSELECT2 AS tag,1 AS Parent,Customers.BusinessEntityID,NULL,Orders.SalesOrderID,Orders.OrderDateFROM Person.Person AS CustomersINNER JOIN Sales.SalesOrderHeader AS OrdersON Orders.CustomerID = Customers.BusinessEntityIDWHERE Customers.BusinessEntityID IN (20722,11712,20708)ORDER BY [Customer!1!CustomerID] DESC, [ORDER!2!OrderID]FOR XML EXPLICIT
Amy Liang Amy Wu Pedro Dominguez
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 CustomersINNER JOIN Sales.SalesOrderHeader AS OrdersON Orders.CustomerID = Customers.BusinessEntityIDORDER BY Orders.SalesOrderIDFOR XML PATH('Customer'), ROOT('Sales')
43700 2005-07-01T00:00:00 43701 2005-07-01T00:00:00 43703 2005-07-02T00:00:00 43704 2005-07-02T00:00:00 43705 2005-07-02T00:00:00
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 OrdersFROM Sales.Customer AS Customer WHERE EXISTS(SELECT 1 FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID = Customer.CustomerID) ORDER BY Customer.CustomerID;
SELECT Customer.CustomerID, Customer.TerritoryID, (SELECT SalesOrderID, [Status] FROM Sales.SalesOrderHeader AS soh WHERE Customer.CustomerID = soh.CustomerID FOR XML AUTO, TYPE ) AS OrdersFROM Sales.Customer AS Customer WHERE EXISTS(SELECT 1 FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID = Customer.CustomerID) ORDER BY Customer.CustomerID;
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 OrdersFROM Sales.Customer AS Customer WHERE EXISTS(SELECT 1 FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID = Customer.CustomerID) ORDER BY Customer.CustomerIDFOR XML PATH('Customer'), ROOT('Sales')
