Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get the ROOT node name from SQL Server

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
like image 967
KS Kian Seng Avatar asked Sep 06 '13 07:09

KS Kian Seng


People also ask

How do I get root in SQL?

To compute the square root of a number, use the SQRT() function. This function takes a number as its argument and returns the square root.

What is a node in SQL?

Database nodes are storage nodes that connect to databases and perform different operations on them, such as update, insert, delete, and select. Each type of Database requires different configuration information.


2 Answers

Use the local-name() function

 select ID, XML.value('local-name(/*[1])','varchar(100)')
 from yourtable
like image 115
podiluska Avatar answered Oct 01 '22 22:10

podiluska


Try this

DECLARE @xml as xml
SET @xml = '<Form1>...</Form1>'
SELECT Nodes.Name.query('local-name(.)') FROM @xml.nodes('//*') As Nodes(Name)
like image 24
Gayathri L Avatar answered Oct 01 '22 21:10

Gayathri L