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?)
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.