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

score 2 · Accepted Answer · answered Dec 11 '15 at 11:35

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

How to select each value for xml node sql server

1 Answers1