TSQL XML Comma delimited list

Question

I am trying to get a comma delimited field. The structure looks very similar to the one below. I'd like to get a comma delimited list for all 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)') AS OrderId,
  @Test.value('(/Order/Address/@street)[1]', 'NVARCHAR(1000)') AS Street,
  @Test.value('(/Order/Address/@city)[1]', 'NVARCHAR(1000)') AS City,
  @Test.value('(/Order/Address/@state)[1]', 'NVARCHAR(1000)') AS State,
  @Test.value('(/Order/Address/@zip)[1]', 'NVARCHAR(1000)') AS Zip,
  @Test.query('Order/Products/Product/text()') AS prods

I am getting those:

"orderid" for OrderId "1234 City World" For Street "City" For City "FL" for State "1245" for Zip. "12345" for Prods.

I would like to get "1,2,3,4,5" for prods.

thanks,

score 1 · Accepted Answer · edited May 23 '17 at 12:05

I was able to reach the result you expect by using this:

SELECT  @Test.value('(/Order/@id)[1]', 'NVARCHAR(1000)') AS OrderId ,
        @Test.value('(/Order/Address/@street)[1]', 'NVARCHAR(1000)') AS Street ,
        @Test.value('(/Order/Address/@city)[1]', 'NVARCHAR(1000)') AS City ,
        @Test.value('(/Order/Address/@state)[1]', 'NVARCHAR(1000)') AS State ,
        @Test.value('(/Order/Address/@zip)[1]', 'NVARCHAR(1000)') AS Zip ,
        ( SELECT    
            STUFF(( SELECT  ',' + Prods.Prod.value('text()[1]','varchar(max)')
                    FROM    @Test.nodes('/Order/Products/Product') AS Prods ( Prod )
                    FOR XML PATH('')
            ), 1, 1, '')
        ) prods

It's kind of confusing, but what I do is to select the Products node and convert it to a "table" of Products then I use the FOR XML and the STUFF in order to concatenate them at the same line.

The STUFF function doesn't work on older versions of SQL Server, so maybe you'll need to adapt this solution, you can try this answer in case you need.

I hope it helps.

TSQL XML Comma delimited list

1 Answers1