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

ITKING

New Member
I am using SQL Server 2008 R2 and my stored procedure takes in a structured XML which has multiple levels like this:\[code\]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>'\[/code\]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 \[code\]<Departments>\[/code\] XML.I can get the user id with the following query but how to get the Departments section as an XML:\[code\]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)\[/code\]It does not allow me to use \[code\]xml\[/code\] as datatype in place of \[code\]nvarchar(max)\[/code\]If details are not clear let me know and I will try to refine it. Any help is appreciated.
 
Back
Top