Talk to the Experts

In the last post, I discussed the nodes() method in my XQuery for the Non-Expert series.  With this next post, we’ll be looking into the query() method.

The main use for the query() method is to retrieve child elements by browsing down to lower levels of an XML document.

Sometimes when the nodes() method is used to browse through an XML document you need to retrieve the XML fragment that was queries.  The query() method can be used to return the XML fragment.

Query Syntax

The general syntax for the query() method is

query ('XQuery')

The XQuery statement that directs the query() method how to browse the XML document or fragment.  For a more detailed explanation of the XQuery statement, read my post on the nodes() method.

The query() function is called as a method off of the column that contains the XML data.  This method can be called the SELECT statement.

Query Setup

To illustrate the two methods listed above for using the query() method let’s setup a table with an XML document.  Run the following query to get everything setup for the examples below:

IF OBJECT_ID('tempdb..#QueryExample') IS NOT NULL
DROP TABLE #QueryExample
GO
CREATE TABLE #QueryExample
(
XMLDocument xml
)
INSERT INTO #QueryExample
VALUES ('<Building type="skyscraper">Sears Tower
<Floor level="1" />
<Floor level="2" />
<Floor level="3">
<Room number="3.1" />
<Room number="3.2" />
</Floor>
<Floor level="4">
<Room number="4.1" />
<Room number="4.2" />
</Floor>
</Building>')
SELECT XMLDocument FROM #QueryExample

Now that we have something to play with, lets dig in and use the query() method.

Query Example

For the first example, the query() method will be used to retrieve all elements nest under some of the parent elements.  In the query we will return the Room elements that are under the Building and Floor elements (“/Building/Floor/Room”).

The XQuery for this will look similar to the following:

SELECT XMLDocument.query('/Building/Floor/Room')
FROM #QueryExample

The query() method above is used in the SELECT statement and browses down from the Building root element to the Room element two levels down.  The query results will be as follows:

The XML output is the following:

These results are quite different than they were for the nodes() method.  With nodes() each XML fragment was broken out to it’s own row.  Where with the query() method all of the elements that match the XQuery string are returned in a single XML fragment.

The other manner in which the query() method can be used is to replace the XQuery statement in the method call with a period, or two.  When a single period is used for the query() string it returns the XML fragment in the XML column.  If two periods are used the XML fragment is returned with its parent elements.

To demonstrate this change the query to the following:

SELECT c.query('.') OnePeriod, c.query('..') TwoPeriods
FROM #QueryExample
CROSS APPLY XMLDocument.nodes('/Building/Floor/Room') as t(c)

The query results will be as follows:

The XML output with a single period query() method is the following:

The XML output with two periods query() method is the following:

As you can see in the results, the query() method returns the XML fragment that was shred by the nodes() method of the query.  This is very useful when shredding and trying to display XML data in a query results when there is a need to determine if the nodes() method is returning the correct information.

Query Wrap-Up

Nothing else to really add to the query() method.  If anything in this post needs further clarity, please comment below.  I expect that as time goes on I will update and expand this post as I learn more about how to leverage the nodes() function.

Related posts:

1. XQuery for the Non-Expert – Nodes
2. XQuery for the Non-Expert – Terminology
3. XQuery for the Non-Expert – XMLNAMESPACE