Extract Value from XML having same tag name in SQL Server

Question

I have XML variable defined below and its value.

I want to fetch the text defined between tag <TextNodeChild> in single query.

Kindly help.

Declare @XMLVariable = 
'<?xml version="1.0"?>
 <root>  
     <TextNodeParent>
         <TextNodeChild>12345</TextNodeChild>
         <TextNodeChild>67890</TextNodeChild>
         <TextNodeChild>12389</TextNodeChild>
     </TextNodeParent>
 </root>'

I need output like this:

12345
67890
12389

Show us what you've tried so far

Marcel
– Marcel

2017-12-05 07:57:12 +00:00
Commented Dec 5, 2017 at 7:57 — Marcel
– Marcel, Commented Dec 5, 2017 at 7:57

Yogesh Sharma · Accepted Answer · 2017-12-05 10:56:35Z

1

You could use the XQuery (i.e. XML query) .nodes() method

SELECT 
    TextNodeParent = n.value('.[1]', 'NVARCHAR(max)') 
FROM
    @XMLVariable.nodes('root/TextNodeParent/*') as p(n)

EDIT : If you want to just the select the TextNodeChild node data then little change in xml path as follow

 @XMLVariable.nodes('root/TextNodeParent/TextNodeChild') as p(n)

Result

TextNodeParent
12345
67890
12389

edited Dec 5, 2017 at 10:56

answered Dec 5, 2017 at 8:06

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

2 Comments

K.shah Over a year ago

Thanks for the solution.

marc_s Over a year ago

That will list all elements below <TextNodeParent> - not jsut the <TextNodeChild> nodes. In the sample, there are no other nodes - so this works - but as a general solution, it could fail if there were other elements stored under the <TextNodeParent> node

marc_s · Accepted Answer · 2017-12-05 10:18:30Z

0

@YogeshSharma's solution works - here - because you have nothing but <TextNodeChild> elements under your <TextNodeParent> node.

However, if you had various node, and you wanted to extract only the <TextNodeChild> ones and get their values (and ignore all others), you'd have to use something like this instead:

SELECT 
    TextNodeParent = XC.value('.', 'INT')
FROM
    @XMLVariable.nodes('root/TextNodeParent/TextNodeChild') as XT(XC)

answered Dec 5, 2017 at 10:18

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Collectives™ on Stack Overflow

Extract Value from XML having same tag name in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related