I am just a medium, SQL Server the Goal

Tag Archives: SQL Server 2000

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 @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
       ) as Name
FROM HumanResources.Employee Employee

—- Output —————–

EmployeeID HireDate Name

1996-07-31 00:00:00

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

1997-02-26 00:00:00

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

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
                 <Name>{string($p/@FirstName)} {string(" ")}
                        {string($p/@LastName)} {string(" ")}></Name>’)
FROM HumanResources.Employee Employee

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.


Working with XML in Microsoft SQL Server

With SQL Server 2000 and later versions of Microsoft SQL Server relational tables can be queries to produce XML output or XML documents can be converted to relational tables.

In this series of articles I will talk about SQL XML features in SQL Server 2000 and then Continue with XML enhancements in SQL Server 2005 and SQL Server 2008. Some of things which I will be talking about are