SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

NESTED FOR XML Queries with SQL Server

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 —————–

EmployeeID HireDate Name
1

1996-07-31 00:00:00

<Contact FirstName="Guy" LastName="Gilbert" />
2

1997-02-26 00:00:00

<Contact FirstName="Kevin" LastName="Brown" />
3

1997-12-12 00:00:00

<Contact FirstName="Roberto" LastName="Tamburello" />
4 1998-01-05 00:00:00 <Contact FirstName="Rob" LastName="Walters" />

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.

EmployeeID Name
1 <Name>Guy Gilbert </Name>
2 <Name>Kevin Brown </Name>
3 <Name>Roberto Tamburello </Name>
4 <Name>Rob Walters </Name>
5 <Name>Thierry D’Hers </Name>
6 <Name>David Bradley </Name>

 

Hope this helps.

Advertisements

One response to “NESTED FOR XML Queries with SQL Server

  1. Pingback: Working with XML in Microsoft SQL Server « SQLUninterrupted

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: