CODEWITHSUNDEEP
sql-serverxqueryxquery-sql
Jono
Jono

Reputation: 2054

What type is returned from nodes() in a SQL Server XML query

If I have a SQL variable of the XML type, it's possible to use it directly as a column of the SELECT clause (even if the value is an XML fragment without a single root element)

DECLARE @Items XML
SET @Items = '<item>one</item><item>two</item>'
SELECT @Items

Yet when I shred that XML variable in any way using the nodes() function, SQL Server complains that

SELECT Items.Item FROM @Items.nodes('/') AS Items (Item)
-- The column 'Item' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four XML data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks.

It's fine when I follow the hints contained in the error message

SELECT Items.Item.query('.') FROM @Items.nodes('/') AS Items (Item)
SELECT Items.Item.query('.') FROM @Items.nodes('//item') AS Items (Item)

But I can't even convert the result back to XML without using one of those "XML data type" methods.

SELECT CAST(Items.Item AS XML) FROM @Items.nodes('/') AS Items (Item)
-- The column that was returned from the nodes() method cannot be converted to the data type xml. It can only be used with one of the four XML data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks.

What is the data type of the Items.Item column and why can't I use it as XML without involving additional methods ( when it's clearly possible to directly select a column of the XML data type?)

Upvotes: 3

Views: 1535

Answers (2)

Martin Smith
Martin Smith

Reputation: 453628

Let's look at a slightly different example.

DECLARE @Items XML
SET @Items = '<data><item>one</item><item>two</item></data>'

SELECT Items.Item.query('.') 
FROM @Items.nodes('data/item') AS Items (Item)

This returns the results below

Items.Item.query('.')
<item>one</item>
<item>two</item>

I have added an additional column below to indicate what the result of Items.Item conceptually would be for each row returned.

Items.Item.query('.') Items.Item
<item>one</item> enter image description here
<item>two</item> enter image description here

This is the same document as the input document to the nodes method is. Just the context node (indicated by the arrow) is set differently in each row.

You can see this by using the .. expression to select the parent of the context node

SELECT Items.Item.query('..') AS [Items.Item.query('..')]
FROM @Items.nodes('data/item') AS Items (Item)

Returns

+-----------------------------------------------+
|            Items.Item.query('..')             |
+-----------------------------------------------+
| <data><item>one</item><item>two</item></data> |
| <data><item>one</item><item>two</item></data> |
+-----------------------------------------------+

If it was possible to select Items.Item directly I doubt that this would be the expectation of users that it would just return the input document. If you wanted to just return the input document you can just select this directly

SELECT Items.Item.query('.') AS [Items.Item.query('.')], @Items
FROM @Items.nodes('data/item') AS Items (Item)

Upvotes: 1

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22275

The official documentation says it is a rowset. Here is an excerpt from it:

The result of the nodes() method is a rowset that contains logical copies of the original XML instances. In these logical copies, the context node of every row instance is set to one of the nodes that is identified with the query expression. This way, later queries can navigate relative to these context nodes.

nodes() Method (xml Data Type)

Upvotes: 1

Related Questions