How to select each value for xml node sql server

Question

I have an xml column within my sql server table where the XML looks like this:

<Alternate>
    <Suggestion>
        <Details>
            <Number>51</Number>
        </Details>
    </Suggestion>
    <Suggestion>
        <Details>
            <Number>53</Number>
        </Details>
    </Suggestion>
</Alternate>

I want to be able to pull out each value for the Number tag into a column that would look like this:

Number
51
53

I have attempted this using CROSS APPLY but it only pulls out the first value for each row:

Number
51
51

The SQL Query i wrote for this looks like this:

SELECT [XML].value('(//Alternate/Suggestion/Details/Number)[1]','nvarchar(255)') as Improvement_Number
FROM Table
CROSS APPLY [XML].nodes('(//Aternate/Suggestion/Details/Number)') AS T2(Improv)

Can someone explain to me what I am doing wrong with this please? Any help would be great

Thanks

Svein Fidjestøl · Accepted Answer · 2015-12-11 11:35:03Z

2

Creating the table and inserting a row like this:

create table alternate ([XML] xml)

insert into alternate values (N'<Alternate>
    <Suggestion>
        <Details>
            <Number>51</Number>
        </Details>
    </Suggestion>
    <Suggestion>
        <Details>
            <Number>53</Number>
        </Details>
    </Suggestion>
</Alternate>')

You can query it in this way:

select 
    b.Improv.value('.', 'nvarchar(255)') Number
from 
    alternate a
    cross apply a.[XML].nodes('//Alternate/Suggestion/Details/Number') b(Improv)

Result:

Number
51
53

answered Dec 11, 2015 at 11:35

Svein Fidjestøl

3,2063 gold badges27 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

How to select each value for xml node sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related