CODEWITHSUNDEEP
sqlsql-serverxml
K.shah
K.shah

Reputation: 1

Extract Value from XML having same tag name in SQL Server

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

Upvotes: 0

Views: 1721

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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

Upvotes: 1

marc_s
marc_s

Reputation: 754268

@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)

Upvotes: 0

Related Questions