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 element per row.[codesyntax lang=”tsql”]

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

[/codesyntax]

 

The Result set

[codesyntax lang=”xml”]





[/codesyntax]

 

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

[codesyntax lang=”tsql”]

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')

[/codesyntax]

 

The Result set

[codesyntax lang=”xml”]





[/codesyntax]

 

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:

[codesyntax lang=”tsql”]

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')

[/codesyntax]

 

The Result set

[codesyntax lang=”xml”]


  
  
  
  
  

[/codesyntax]

 

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:

[codesyntax lang=”tsql”]

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

[/codesyntax]

 

The Result set

[codesyntax lang=”xml”]


  
    14501
    Bryan J Gray 
    43700
  
  
    11003
    Jenna W Young 
    43701
  
  
    16624
    Nichole A Goel 
    43703
  
  
    11005
    Joe Madan 
    43704
  
  
    11011
    Joe D Rana 
    43705
  

[/codesyntax]

 

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.[codesyntax lang=”tsql”]

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

[/codesyntax]

 

The Result set

[codesyntax lang=”xml”]


  
  
  


  
  

[/codesyntax]

 

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.[codesyntax lang=”tsql”]SELECT TOP 10 ProductID, Name, ColorFROM Production.Product AS ProductsORDER BY ProductIDFOR XML AUTO[/codesyntax]The Result set[codesyntax lang=”xml”][/codesyntax]

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.[codesyntax lang=”tsql”]SELECT TOP 10 ProductID, Name, ColorFROM Production.Product AS ProductsORDER BY ProductIDFOR XML AUTO, ELEMENTS XSINIL[/codesyntax]The Result set[codesyntax lang=”xml”]  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[/codesyntax]

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:[codesyntax lang=”tsql”]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[/codesyntax][info] Note that for specifying column to be returned as element instead of attribute you need to add […!ELEMENT] to the end of column name.[/info] The Result set[codesyntax lang=”xml”]Amy Liang Amy Wu Pedro Dominguez [/codesyntax]

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.[codesyntax lang=”tsql”]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’)[/codesyntax]The Result set[codesyntax lang=”xml”]            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      [/codesyntax]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.[info]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. [/info]

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:[codesyntax lang=”tsql”]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;[/codesyntax]This query returns the Orders subquery as a varchar column rather than XML. Now look at the following example:[codesyntax lang=”tsql”]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;[/codesyntax]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.[codesyntax lang=”tsql”]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’)[/codesyntax]The Result Set[codesyntax lang=”xml”]                                                                                          [/codesyntax]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.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan