Extracting element names from XML in SQL Server 2012

Question

I have an xml doc like this:

  <root>
     <e1>
       <l2>
       ..
       </l2>
     </e1>
     <e2>
      ...
     </e2>
     ...
    </root>

I want to extract the list of direct children of an element only the element tag name, for example for root I only want to get e1, and e2 (just the tag(element)name not the whole element) and for e1 I want to get l2 again just the tagname

Is it possible to do this in SQL Server 2012?

marc_s · Accepted Answer · 2014-07-11 05:04:57Z

4

Something like this?

DECLARE @input XML = '<root>
     <e1>
       <l2>
         <data1>test</data1>
       </l2>
       <l3>
         <data3>test3</data3>
       </l3>
     </e1>
     <e2>
        <data1>test</data1>
        <data2>test2</data2>
     </e2>
    </root>'

To get the immediate descendant nodes under <root>, you can use this query:

SELECT
    XC.value('local-name(.)', 'varchar(20)')
FROM 
    @input.nodes('/root/*') AS XT(XC)

Returns:

e1
e2

And this query to get the descendants of <e2>:

SELECT
    XC.value('local-name(.)', 'varchar(20)')
FROM 
    @input.nodes('/root/e2/*') AS XT(XC)

returns:

data1
data2

answered Jul 11, 2014 at 5:04

marc_s

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

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

2 Comments

Fred Jand Over a year ago

Thanks, this works fine.I wonder where this has been documented 'local-name'?

marc_s Over a year ago

@FredJand: see here - official MSDN documentation by Microsoft

Collectives™ on Stack Overflow

Extracting element names from XML in SQL Server 2012

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related