Select query from XML data

Question

I am trying to get result from XML data but only getting a value of first node.

create table #temp(xmlString nvarchar(max))
insert into #temp (xmlString) values 
('<?xml version="1.0" ?><response status = "ERROR">
<error>Error1</error>
<error>Error2</error>
</response>')

I want a result :

Error1, Error2

Please help. Thanks

How did your attempt to solve this problem end up?

OzrenTkalcecKrznaric
– OzrenTkalcecKrznaric

2013-07-20 20:44:36 +00:00
Commented Jul 20, 2013 at 20:44 — OzrenTkalcecKrznaric
– OzrenTkalcecKrznaric, Commented Jul 20, 2013 at 20:44

roman · Accepted Answer · 2013-07-20 21:38:24Z

3

select
    x.c.value('.', 'nvarchar(128)') as value
from (select cast(xmlString as xml) as data from temp) as t
    outer apply t.data.nodes('/response/error') as x(c)

SQL FIDDLE EXAMPLE

answered Jul 20, 2013 at 21:38

roman

118k30 gold badges205 silver badges209 bronze badges

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

1 Comment

Maddy Over a year ago

Thanks Roman, i got what i want from your answer thanks again

Maddy · Accepted Answer · 2013-07-21 16:45:20Z

0

correct answer

select STUFF((select ',' +  x.c.value('.', 'nvarchar(max)') 
from (select cast(xmlString as xml) as data from #temp)
as t outer apply t.data.nodes('/response/error')
as x(c)for xml path('')), 1, 1, '') as Errors

edited Jul 21, 2013 at 16:45

answered Jul 20, 2013 at 22:31

Maddy

9273 gold badges10 silver badges25 bronze badges

Collectives™ on Stack Overflow

Select query from XML data

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related