sql server - TSQL XML Comma delimited list -

i trying comma delimited field. structure looks similar 1 below. i'd comma delimited list nodes below.

declare @test xml = '      <order id="orderid">       <products>         <product>1</product>         <product>2</product>         <product>3</product>         <product>4</product>         <product>5</product>       </products>       <address street="1234 city world" zip="12345" city="city" state="fl"></address> </order>'  select    @test.value('(/order/@id)[1]', 'nvarchar(1000)') orderid,   @test.value('(/order/address/@street)[1]', 'nvarchar(1000)') street,   @test.value('(/order/address/@city)[1]', 'nvarchar(1000)') city,   @test.value('(/order/address/@state)[1]', 'nvarchar(1000)') state,   @test.value('(/order/address/@zip)[1]', 'nvarchar(1000)') zip,   @test.query('order/products/product/text()') prods  

i getting those:

"orderid" orderid "1234 city world" street "city" city "fl" state "1245" zip. "12345" prods.

i "1,2,3,4,5" prods.

thanks,

i able reach result expect using this:

select  @test.value('(/order/@id)[1]', 'nvarchar(1000)') orderid ,         @test.value('(/order/address/@street)[1]', 'nvarchar(1000)') street ,         @test.value('(/order/address/@city)[1]', 'nvarchar(1000)') city ,         @test.value('(/order/address/@state)[1]', 'nvarchar(1000)') state ,         @test.value('(/order/address/@zip)[1]', 'nvarchar(1000)') zip ,         ( select                 stuff(( select  ',' + prods.prod.value('text()[1]','varchar(max)')                        @test.nodes('/order/products/product') prods ( prod )                     xml path('')             ), 1, 1, '')         ) prods 

it's kind of confusing, select products node , convert "table" of products use for xml , stuff in order concatenate them @ same line.

the stuff function doesn't work on older versions of sql server, maybe you'll need adapt solution, can try this answer in case need.

i hope helps.