Nodes from XML query instead of variable

Question

<UserSettings>
   <ActiveStaff>
    <int>2063</int>
    <int>2062</int>
    <int>5</int>
    <int>10</int>
    <int>2064</int>
   </ActiveStaff>
</UserSettings>

This is the XML I have, it's part of a varchar column. In MS SQL if I declare an XML variable, set it to some XML (column User.Settings is varchar so it is cast to XML) and then use nodes on it it provides a 5 columns with the values I need.

DECLARE @xml XML
SET @xml= (SELECT CAST(CAST(User.Settings AS NTEXT) AS XML) from User where User_id = 3)

SELECT T.c.query('.').value('.','int')   
FROM @xml.nodes('//UserSettings/ActiveStaff/int') T(c)

However, if I try to skip the variable and use a query.nodes, it cannot be executed:

SELECT T.c.query('.').value('.','int')   
FROM (SELECT CAST(CAST(User.Settings AS NTEXT) AS XML) from User where User_id = 
3).nodes('//UserSettings/ActiveStaff/int') T(c)

Is there a way to bypass using variables and use a query instead? I need this because later I need to query the XML in a where condition, and I cannot create any extra tables. Any alternative approaches to query a XML string in a column are also welcomed

Answer 1

You need to CROSS APPLY to the nodes column. Your derived table will return a dataset, not xml, and the nodes method is specific to the xml datatype:

CREATE TABLE #YourTable (User_id int,Settings xml); --I assume your XML data is in an XML data type, right? Why wouldn't it be?
INSERT INTO #YourTable (User_id,Settings)
VALUES(3,'<UserSettings>
   <ActiveStaff>
    <int>2063</int>
    <int>2062</int>
    <int>5</int>
    <int>10</int>
    <int>2064</int>
   </ActiveStaff>
</UserSettings>');
GO
SELECT Staff.[int].value('(./text())[1]','int') AS [int]
FROM #YourTable YT
     CROSS APPLY YT.Settings.nodes('/UserSettings/ActiveStaff/int') Staff([int]);
GO
DROP TABLE #YourTable;