Continuing in my discussion of XQuery in SQL Server, let me discuss another XML data type method: .query().  The .query() method is a great illustration of the bridging concept I discussed earlier.  Simply put, this method allows you to run an XQuery against the XML data type, and return the results of that query as an XML document to SQL Server.  Using our example from the .exists() post:

DECLARE @T TABLE ( ID INT, x XML )

INSERT  INTO @T
        ( ID, x )
VALUES  ( 1, '<root><plant><fruit>tomato</fruit></plant></root>' )
,       ( 2, '<root><plant><fruit>banana</fruit></plant></root>' )
,       ( 3, '<root><plant><vegetable>tomato</vegetable></plant></root>' )

SELECT x.query('.') 
FROM @T 

Note that the parameter supplied to the method is an XQuery statement consisting of a single axis (.), which refers to the current node.  Since SQL Server’s implementation of XQuery starts at the top and works it’s way down the document, the current node is the entire document.  Running that query will give you the following output:

which isn’t very exciting.  However, if we begin to build out a XPath statement with a node test and a predicate, we can begin to narrow down the results quickly:

SELECT ID, x.query('.//fruit[text()="banana"]')
FROM @T t

And here’s the results:

While the results may be simple, there’s a lot going on behind the scenes, and it may have returned something different than what you expected.  Remember that when you execute an XQuery method against an XML column, there’s actually two queries happening:

For the XQuery portion, the XPath axis, node test, and predicate specified that we were looking for an XML node that was a fruit and had a value of a banana; this only existed in the XML in row 2, so the node was returned for row 2, but row 1 and row 3 were empty.  The SQL query then ran, and returned all rows in the table because there was no WHERE clause filtering the results.  If we only wanted to return the results where the XQuery returned a node, we could move the .query() method to the where clause.

One thing to always remember is that regardless of how specific your XQuery is, the type of the data being returned will always be XML.  For example, if you specify the text() function as part of the axis, and not the predicate:

SELECT ID, x.query('.//text()')
FROM @T t 

You’ll get what looks like a table with a column of character values representing the values of the various nodes:

However, the visual representation of the result set uses hyperlinks to indicate that it’s an XML column; if you click on the value (banana), the XML explorer will open up, showing you that it is an XML fragment, albeit one that has no tags.  If you need to return the data as the actual datatype, you’ll either need to use a SQL method (like CAST or CONVERT), or the .value() method (to be discussed soon).

Summary

Wrapping up with a few key points:

1. The query() method allows you to specify an XQuery statement against an XML document, and
2. It always returns an XML document.

Tags: SQL ServerXMLXQuery