1

I cannot seem to get a [comma-]delimited string out of an XML doc using just SQL Server 2005 XQuery.

I have:

<AAA>
  <Name>Name_A</Name>
  <Value>Val_A</Value>
</AAA>
<AAA>
  <Name>Name_B</Name>
  <Value>Val_B</Value>
</AAA>
<AAA>
  <Name>Name_C</Name>
  <Value>Val_C</Value>
</AAA>
... (etc.)

... and would like to get Val_A,Val_B,Val_C... - a comma-delimited string.

I can convert to a table then back to string with FOR XML, but thought there is a direct way.

Thank you.

user984528
  • 13
  • 4

3 Answers3

1

How about this - this will work for any number of <AAA> nodes in an XML variable:

DECLARE @Input XML = '<AAA>
  <Name>Name_A</Name>
  <Value>Val_A</Value>
</AAA>
<AAA>
  <Name>Name_B</Name>
  <Value>Val_B</Value>
</AAA>
<AAA>
  <Name>Name_C</Name>
  <Value>Val_C</Value>
</AAA>'

SELECT
    STUFF(
    (SELECT 
        ',' + AAA.value('(Value)[1]', 'varchar(20)')
     FROM
        @Input.nodes('/AAA') AS Node(AAA)
     FOR XML PATH('')
    ), 1, 1, '')

Output is:

Val_A,Val_B,Val_C
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Ah, I did not know that adding a string (",") to the XML would work and was using a CTE with the same .nodes() query to then do the FOR XML PATH separately! Excellent, thank you. – user984528 Oct 07 '11 at 21:10
0
declare @xml xml = '
<AAA>
  <Name>Name_A</Name>
  <Value>Val_A</Value>
</AAA>
<AAA>
  <Name>Name_B</Name>
  <Value>Val_B</Value>
</AAA>
<AAA>
  <Name>Name_C</Name>
  <Value>Val_C</Value>
</AAA>
'

select @xml.value('(/AAA[1]/Value)[1]', 'varchar(10)')+','+
       @xml.value('(/AAA[2]/Value)[1]', 'varchar(10)')+','+
       @xml.value('(/AAA[3]/Value)[1]', 'varchar(10)')
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
0

How about

string-join(/AAA/Value, ",")

(You might need to adapt the beginning of the Path expression depending on how you read your input)

Ghislain Fourny
  • 6,971
  • 1
  • 30
  • 37