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

Question

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.

You want to obtain the whole Departments node as-is from the XML, not the Department ID and name values for each? — Darth Continent
– Darth Continent, Commented Jun 13, 2012 at 18:10

marc_s · Accepted Answer · 2012-06-13 18:19:16Z

6

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.

answered Jun 13, 2012 at 18:19

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related