CODEWITHSUNDEEP
sql-serverxml
Anitha
Anitha

Reputation: 111

Getting multiple values from same xml column in SQL Server

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?

Upvotes: 2

Views: 1866

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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

Upvotes: 1

marc_s
marc_s

Reputation: 754258

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

Upvotes: 1

Related Questions