T-SQL Query XML Data

Question

I have the following formatted XML:

<ROOT>
 <table1>
  <row ID="1".... \>
 </table1>
 <table2>
  <row ID="1".... \>
 </table2>
...
</ROOT>

I would like to produce a query that returns:

TableName
table1
table2 
...

In other words, the value of each node under

Answer 1

 DECLARE @data XML;

SET @data = 

N'
<root>
 <table1>
  <row ID="1"/>
 </table1>
 <table2>
  <row ID="1"/>
 </table2>
</root>';


SELECT
    T.myAlias.value('fn:local-name(..)', 'nvarchar(50)') as ParentOf_RowElement_Name
FROM 
    @data.nodes('//row') AS T(myAlias)

;



ParentOf_RowElement_Name
--------------------------------------------------
table1
table2

OR

DECLARE @data XML;

SET @data = 

N'
<root>
 <table1>
  <row ID="1"/>
 </table1>
 <table2>
  <row ID="1"/>
 </table2>
</root>';


SELECT
    T.myAlias.value('fn:local-name(.)', 'nvarchar(50)') as ChildOf_RootElement_Name
FROM 
    @data.nodes('//root/*') AS T(myAlias)
;





ChildOf_RootElement_Name
--------------------------------------------------
table1
table2