Getting multiple values from same xml column in SQL Server

Question

I want to get the values from same xml node under same element.

Sample data:

I have to select all <award_number> values.

This is my SQL code:

DECLARE @xml XML;

DECLARE @filePath varchar(max);

SET @filePath = '<workFlowMeta><fundgroup><funder><award_number>0710564</award_number><award_number>1106058</award_number><award_number>1304977</award_number><award_number>1407404</award_number></funder></fundgroup></workFlowMeta>'

SET @xml = CAST(@filePath AS XML);

SELECT                         
    REPLACE(Element.value('award_number','NVARCHAR(255)'), CHAR(10), '') AS award_num
FROM    
    @xml.nodes('workFlowMeta/fundgroup/funder') Datalist(Element);

Can't change this @xml.nodes('workFlowMeta/fundgroup/funder'), because I'm getting multiple node values inside funder node.

Can anyone please help me?

marc_s · Accepted Answer · 2017-05-27 09:38:24Z

1

Since those <award_number> nodes are inside the <funder> nodes, and there could be several <funder> nodes (if I understood your question correctly), you need to use two .nodes() calls like this:

SELECT                         
    XC.value('.', 'int')
FROM    
    @xml.nodes('/workFlowMeta/fundgroup/funder') Datalist(Element)
CROSS APPLY
    Element.nodes('award_number') AS XT(XC)

The first .nodes() call gets all <funder> elements, and then the second call goes into each <funder> element to get all <award_number> nodes inside of that element and outputs the value of the <award_number> element as a INT (I couldn't quite understand what you're trying to do to the <award_number> value in your code sample....)

answered May 27, 2017 at 9:38

marc_s

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

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

2 Comments

Gottfried Lesigang Over a year ago

Hi, I "plused" yours, as it was earlier than mine and is a good answer of course...

Anitha Over a year ago

Thank you marc. its really helped me.

Gottfried Lesigang · Accepted Answer · 2017-05-27 10:22:44Z

Your own code was very close, but

You are diving one level to low
You need to set a singleton XPath for .value(). In most cases this means a [1] at the end)

As you want to read many <award_number> elements, this is the level you have to step down in .nodes(). Reading these element's values is easy, once you have your hands on it:

SELECT                         
    REPLACE(Element.value('text()[1]','NVARCHAR(255)'), CHAR(10), '') AS award_num
FROM    
    @xml.nodes('/workFlowMeta/fundgroup/funder/award_number') Datalist(Element);

What are you trying to do with the REPLACE()?
If all <arward_number> elements contain valid numbers, you should use int or bigint as target type and there shouldn't be any need to replace non-numeric characters. Try it like this:

SELECT Element.value('text()[1]','int') AS award_num
FROM @xml.nodes('/workFlowMeta/fundgroup/funder/award_number') Datalist(Element);

If marc_s is correct...
... and you have to deal with several <funder> groups, each of which contains several <award_number> nodes, go with his approach (two calls to .nodes())

Collectives™ on Stack Overflow

Getting multiple values from same xml column 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