How to get the ROOT node name from SQL Server

Question

I have a table where ID is integer and XML is XML data type.

ID   XML
----------------------
1    <Form1>...</Form1>
2    <Form1>...</Form1>
3    <Form2>...</Form2>
4    <Form3>...</Form3>

How do I get the result below?

ID   XML
-------------
1    Form1
2    Form1
3    Form2
4    Form3

score 32 · Accepted Answer · answered Sep 06 '13 at 07:47

32

Use the local-name() function

 select ID, XML.value('local-name(/*[1])','varchar(100)')
 from yourtable

answered Sep 06 '13 at 07:47

podiluska

score 2 · Answer 2 · answered Sep 06 '13 at 07:48

2

Try this

DECLARE @xml as xml
SET @xml = '<Form1>...</Form1>'
SELECT Nodes.Name.query('local-name(.)') FROM @xml.nodes('//*') As Nodes(Name)

answered Sep 06 '13 at 07:48

Gayathri L

We were using this method, but we ran into issues with "local-name(.)" being a slow operation. replacing the . with /*[1] as indicated in the above answer gave us better performance. – bmg002 Nov 28 '16 at 17:58

2 Answers2