CODEWITHSUNDEEP
sql-serverxml
Fred Jand
Fred Jand

Reputation: 699

Extracting element names from XML in SQL Server 2012

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?

Upvotes: 2

Views: 1131

Answers (1)

marc_s
marc_s

Reputation: 754278

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

Upvotes: 4

Related Questions