XML PATH value in SQL SERVER

Question

i am using XML PATH to bind a multi valued column, the query is working fine but the result is in xml form and i want to convert it into simple text Here is the Query

 SELECT soo.order_id,
 sum(oo.total)AS total,
 items=(SELECT si.item_name FROM sub_order i,Items si
 WHERE i.order_id=soo.order_id and si.item_id=i.item_id  FOR XML PATH(''))
 FROM Orders oo
 INNER JOIN Sub_Order soo ON oo.order_id = soo.order_id
 INNER JOIN Items
 ON soo.item_id = Items.item_id 
 group by soo.order_id

and result of "items" field is like

 <item_name>cake</item_name><item_name>soap</item_name>

but i want it to be like cake,soap

GriGrim · Accepted Answer · 2013-11-13 10:44:55Z

2

Replace

items=(SELECT si.item_name FROM sub_order i,Items si
 WHERE i.order_id=soo.order_id and si.item_id=i.item_id  FOR XML PATH(''))

with

items=STUFF((SELECT ',' + si.item_name FROM sub_order i,Items si
 WHERE i.order_id=soo.order_id and si.item_id=i.item_id  FOR XML PATH('')),1,1,'')

answered Nov 13, 2013 at 10:44

GriGrim

2,9511 gold badge24 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

XML PATH value in SQL SERVER

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related