Extracting the name of the root node in XML using SQL

Question

From the below table,

Is there a way to extract the name of the Root node 'Main' alone from the column 'XML_data' using SQL server?

UPDATE:

DECLARE @ProdID int 

create table #xmldata(id int,data xml)
insert into #xmldata
select
id = '011', 
data =  '<Root>  
<ProductDescription ProductID="1" ProductName="Road Bike">  
<Features>  
  <Warranty>1 year parts and labor</Warranty>  
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>  
</Features>  
</ProductDescription>  
</Root>' 

select * from #xmldata

SET @ProdID =  #xmldata.data.value('(/Root)[1]', 'varchar' )  
SELECT @ProdID  

drop table #xmldata

Can you share the code you wrote after you read the SQL Server XML docs that did not produce the desired output? — dfundako
– dfundako, Commented Jul 2, 2018 at 18:49
Added the code here. But pls read my question again. I don't know how to extract the data at all which means I need support to write for data extraction. — Rick
– Rick, Commented Jul 2, 2018 at 18:56
I updated your temp table definition to make [data] an XML column so that you can use XML functions on the column. They don't work on VARCHAR columns. Also, your script does not align with what is in the table in the screenshot. — squillman
– squillman, Commented Jul 2, 2018 at 19:26

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

Is there a way to extract the name of the Root node 'Main' alone from the column 'XML_data' using SQL server?

Yes, it is possible:

SELECT s.c.value('local-name(.)','varchar(100)') AS root_name, sub.*
FROM #xmldata sub
CROSS APPLY sub.data.nodes('/*') s(c);

DBFiddle Demo

EDIT:

Or using .query() method:

SELECT sub.data.query('local-name(/*[1])') AS root_name, sub.*
FROM #xmldata sub;

DBFiddle Demo2

EDIT 2:

As mentioned by @Shnugo you could use .value() method without .nodes():

SELECT sub.data.value('local-name((/*)[1])','varchar(100)') AS root_name, sub.*
FROM #xmldata sub;

DBFiddle Demo3

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 2, 2018 at 19:49

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

2 Comments

Gottfried Lesigang Over a year ago

The easiest is without nodes() or .query(). Just sub.data.value('local-name((/*)[1])','varchar(100)')

Lukasz Szozda Over a year ago

@Shnugo 👍 Thanks, I like this version.

squillman · Accepted Answer · 2018-07-02 20:03:28Z

1

Here is a (correct) script to create the table in your screenshot. The query shows how to select the value of the name node from each row:

DROP TABLE IF EXISTS #xmldata;
CREATE TABLE #xmldata
(
    cid INT,
    cidtype CHAR,
    xml_data XML
);

INSERT #xmldata SELECT 1001,'N','<Main><ID>1001</ID><details><name>John</name><age>12</age></details></Main>';
INSERT #xmldata SELECT 1001,'N','<Main><ID>1003</ID><details><name>Diane</name><age>25</age></details></Main>';
INSERT #xmldata SELECT 1001,'N','<Main><ID>1004</ID><details><name>Kippy</name><age>26</age></details></Main>';

SELECT xml_data.value('(/Main/details/name)[1]','varchar(100)') AS [name]
FROM #xmldata;

edited Jul 2, 2018 at 20:03

answered Jul 2, 2018 at 19:36

squillman

13.7k3 gold badges45 silver badges65 bronze badges

4 Comments

Lukasz Szozda Over a year ago

May I ask how does it solve OP's question(that is getting root element name)?

squillman Over a year ago

@LukaszSzozda I interpreted that as "get the value of the name node". Since all of the records in the sample data have the same root node name that seemed to make the most logical sense.

Lukasz Szozda Over a year ago

I wouldn't say so based on "Is there a way to extract the name of the Root node 'Main' alone from the column 'XML_data' using SQL server?". But it may be a bit vague.

squillman Over a year ago

Yeah, seems you interpreted it more correctly than I did. Vague indeed.

Collectives™ on Stack Overflow

Extracting the name of the root node in XML using SQL

2 Answers 2

EDIT:

EDIT 2:

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDIT:

EDIT 2:

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related