SQL Parse XML String into rows of a table

Question

I have an XML variable which I need to query in SQL and return a row for each value:

declare @info xml
select @info = '<parameters><locality_id>303</locality_id><locality_id>PC_303</locality_id></parameters>'

There parameters node can have 1 or more locality_id values (2 in the case above) The output should be

locality_id
303
PC_303

I have gotten as far as getting two rows as the result, but the values are not coming up properly, it just shows the first value twice:

declare @info xml
select @info = '<parameters><locality_id>303</locality_id><locality_id>PC_303</locality_id></parameters>'
select feed.x.value('(//locality_id)[1]','NVARCHAR(255)')
from @info.nodes('//parameters/locality_id') feed(x)

How can i get what we need for this?

cordsen · Accepted Answer · 2011-07-07 05:04:20Z

3

The problem is in (//locality_id)[1] which is saying "get me the first locality_id at any level". Since you are already selecting the nodes you need with the nodes method, you can just select the value from the current context using ..

declare @info xml
select @info = '<parameters><locality_id>303</locality_id><locality_id>PC_303</locality_id></parameters>'
select feed.x.value('.','NVARCHAR(255)')
from @info.nodes('//parameters/locality_id') feed(x)

edited Jul 7, 2011 at 5:04

answered Jul 7, 2011 at 4:57

cordsen

1,69112 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

SQL Parse XML String into rows of a table

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