How to read a part of an XML as an XML in SQL Server 2008 R2

sql-server-2008-r2, t-sql, xml, xquery

Solution

You're close - just use `.query('.')` for your second column:

SELECT
      T.C.value('(../@id)','int') AS UserID, 
      T.C.query('.') AS DepartmentsXML 
FROM 
@XML.nodes('/Main/User/Departments[@isSingle="0"]') AS T(C)

This will return the XML fragment as selected by the `.nodes()` call, as an XML.

Problem

I am using SQL Server 2008 R2 and my stored procedure takes in a structured XML which has multiple levels like this: ``` DECLARE @XML xml = '<Main> <User id="1"> <Departments isSingle="0"> <Department id="1">Admin</Department> <Department id="2">HR</Department> <Department id="3">Development</Department> </Departments> </User> <User id="2"> <Departments isSingle="1"> <Department id="1">Admin</Department> </Departments> </User> </Main>' ``` From the above example I want to get 2 columns for Users with multiple departments (isSingle="0") where first column is the user id and second column is the whole `<Departments>` XML. I can get the user id with the following query but how to get the Departments section as an XML: ``` SELECT T.C.value('(../@id)','int') AS UserID , T.C.value('(../Departments)[1]','nvarchar(max)') AS DepartmentsXML FROM @XML.nodes('/Main/User/Departments[@isSingle="0"]') AS T(C) ``` It does not allow me to use `xml` as datatype in place of `nvarchar(max)` If details are not clear let me know and I will try to refine it. Any help is appreciated.

Original source