FOR XML modes

Why retrieve data as XML structure?

1. You need to pass the data as XML structure to others like outside applications
2. Sometime if you retrieve the data as XML structure is easier in read or describable more than if you retrieve it as ROWS AND COLUMNS like for example if you retrieve the authors and their books.

To retrieve the result set as XML structure there are four modes:

1. FOR XML RAW
2. FOR XML AUTO
3. FOR XML EXPLICIT
4. FOR XML PATH

So let's start by FOR XML RAW: It returns each row as an XML element and each column as an XML attribute.

Let's see examples:

USE AdventureWorks
GO

SELECT TOP 3 ProductID
            ,Name
            ,ProductNumber
FROM   Production.Product
FOR XML RAW

Result set of the above query:

--You can specify the name of root as well as name of elements
SELECT TOP 3 ProductID
            ,Name
            ,ProductNumber
FROM   Production.Product
FOR XML RAW('Product'),ROOT('Products')

Result set of the above query:

Note:

Simply remove the attribute if the value is NULL.So you can define you result as separate it element to show the empty value or Null.

SELECT TOP 3 ProductID
            ,Name
            ,ProductNumber
FROM   Production.Product
FOR XML RAW('Product'),ROOT('Products'),ELEMENTS

Now let's start by FOR XML AUTO: It returns result as hierarchy structure and only one level supported.

SELECT TOP 3 p.ProductID
            ,p.Name
            ,pm.Name as 'Model'
FROM   Production.Product p
INNER JOIN Production.ProductModel pm
ON p.ProductModelID = pm.ProductModelID
FOR XML AUTO,ROOT('Products')

Now let's start by FOR XML PATH: allows for the easy creation of different XML structures by simply interpreting column names specified using an XPath-like expression.

Let's see this example:

SELECT TOP 4
    ProductID AS 'ProductID'
    ,ProductNumber AS "comment()"
    ,CAST('<Break/>' AS XML) AS "node()"
FROM Production.Product p
FOR XML PATH('Product'), ROOT('Products');

Result set of the above query:

Now let's start by FOR XML EXPLICIT: provides very specific control over your XML structure.

But if you want to use it there are a set of rules to follow them to build your select statement.

Some of these rules:

1. You must include two columns in your select statement:
    a. Tag column with a numerical value for each level of the hierarchy
    b. Parent column with a numerical value based in your Tag column value
2. you must create a SELECT statement for each level of that hierarchy and use UNION ALL clauses to join those statements.
Let's see this example:

USE AdventureWorks
GO

SELECT 1 AS Tag, 
        NULL AS Parent,
        ProductID AS [Product!1!ProductID], 
        NULL AS [ProductName!2!Name!ELEMENT]
FROM Production.Product  
WHERE Name like 'a%'
UNION ALL
SELECT 2 AS Tag, 
        1 AS Parent,
        ProductID , 
        Name
FROM Production.Product  
WHERE Name like 'a%'
FOR XML EXPLICIT;

Result set of the above query:

Note:

Notice an alias to ProductID, Name column. Notice I use a very specific structure to define the alias name