SQLUNINTERRUPTED

Continuing with the series on XML, today we will talk about NESTED XML queries in SQL Server. The ability to write NESTED queries with FOR XML was introduced in SQL 2005 and was largely possible because of the introduction of XML data type in SQL. The results from a nested query could be passed on as an XML to the outer query, where it can be further processed using one of the XQuery methods.

For a complete list of all related articles please refer here.

A Simple example of a nested FOR XML query can be

DECLARE @x xml, @i datetime
SET @x=(SELECT EmployeeID, Title, HireDate
        FROM HumanResources.Employee
        WHERE EmployeeID < 5
        FOR XML RAW, TYPE)
SELECT @x
select @i=@x.value(‘/row[1]/@HireDate[1]’, ‘datetime’)
select @i

<<<< Output >>>>
<row EmployeeID="1" Title="Production Technician – WC60" HireDate="1996-07-31T00:00:00" />
<row EmployeeID="2" Title="Marketing Assistant" HireDate="1997-02-26T00:00:00" />
…

1996-07-31 00:00:00.000

<<<< Output >>>>

The TYPE directive is of importance here. Without the type directive the results would be returned in a nvarchar(max) format. In the above query if we do not Specify the TYPE directive, but declare @x as XML, SQL will try to do the automatic conversion and allow you to process the result as an XML type. But if we declare the variable as nvarchar(max) and then try to use the XQuery it would fail.

Now lets take a more complex example of the Nested FOR XML queries.

SELECT EmployeeID, HireDate,
       (select Contact.FirstName, Contact.LastName
            from Person.Contact Contact
            where Contact.ContactID = Employee.ContactID
        FOR XML AUTO, TYPE
       ) as Name
FROM HumanResources.Employee Employee
ORDER BY EmployeeID

—- Output —————–

In the above query, we are returning the employee names as an XML. If we specify a FOR XML in the outer query also, we will get an XML style output.

As a final Example, lets write a query which will use the inner query and perform some XML type operations on it.

SELECT EmployeeID,
( SELECT Contact.FirstName, Contact.LastName
   FROM Person.Contact Contact
   –where Contact.ContactID = Employee.ContactID
   FOR XML AUTO, type ).query
                (‘for $p in /Contact
                    return
                 <Name>{string($p/@FirstName)} {string(" ")}
                        {string($p/@LastName)} {string(" ")}></Name>’)
FROM HumanResources.Employee Employee
ORDER BY EmployeeID
FOR XML AUTO, TYPE

Here we are using the inner query as an XML and doing XPath() query on this. We are using a special construct of XPath() queries called the FWOR statement to query the XML returned by the inner query. Below is the output.

Hope this helps.