How to make XML values comma separated using XPath

Question

I have an XML column with sample values as

<error>
  <errorno>BL04002055</errorno>
  <description>Smart Rule PROJECT_COUNTRYCODE_VAL Violated</description>
  <description2>Country Code is required</description2>
  <correction />
</error>
<error>
  <errorno>BL01001973</errorno>
  <description />
  <description2>Error While Saving the Project info</description2>
  <correction />
</error>
<error>
  <errorno>Unable to Create Custom Object</errorno>
  <description />
  <description2>Smart Rule PROJECT_COUNTRYCODE_VAL Violated: Country Code is required
Error While Saving the Project info</description2>
  <correction />
</error>

I want to select description2 values comma separated

select *
--, Response.value(''/error/description2/text()'', 'varchar(8000)') as parsedString
, Response.query('/error/description2/text()') as parsedString
 from #temp

Two problems here.

1. I am not able to run value function in query above.
2. Using query, I get values concatenated without space or comma. So I need to add some space or comma on concatenated values.

Answer 1

SQL Server does not implement the xPath function string-join, so you would need to adopt a two step process, the first would be to extract all the terms to rows using nodes();

SELECT  n.value('.', 'VARCHAR(100)') AS parsedString
FROM    #temp AS t
        CROSS APPLY t.Response.nodes('/error/description2') r (n);

Which gives you your values as rows:

parsedString
----------------------------------------------------------------------------
Country Code is required
Error While Saving the Project info
Smart Rule PROJECT_COUNTRYCODE_VAL Violated: Country Code is required
Error While Saving the Project

Then you can add your delimeter and concatenate them back up, using FOR XML PATH(''), TYPE, and finally use STUFF to remove the first delimeter:

SELECT  STUFF(( SELECT  ',' + n.value('.', 'VARCHAR(100)') AS parsedString
                FROM    #temp AS t
                        CROSS APPLY t.Response.nodes('/error/description2') r (n)
                FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS ParsedString;

---

FULL WORKING EXAMPLE

DECLARE @X XML = '<error>
  <errorno>BL04002055</errorno>
  <description>Smart Rule PROJECT_COUNTRYCODE_VAL Violated</description>
  <description2>Country Code is required</description2>
  <correction />
</error>
<error>
  <errorno>BL01001973</errorno>
  <description />
  <description2>Error While Saving the Project info</description2>
  <correction />
</error>
<error>
  <errorno>Unable to Create Custom Object</errorno>
  <description />
  <description2>Smart Rule PROJECT_COUNTRYCODE_VAL Violated: Country Code is required
Error While Saving the Project info</description2>
  <correction />
</error>';

SELECT  STUFF(( SELECT  ',' + n.value('.', 'VARCHAR(100)') AS parsedString
                FROM    (SELECT @X) AS t (Response)
                        CROSS APPLY t.Response.nodes('/error/description2') r (n)
                FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS ParsedString;

Answer 2

Alternatively, you can use combination of XQuery for and if..else statements to construct comma-separated value :

DECLARE @xml XML = 'your xml string here'

SELECT CONVERT(VARCHAR(MAX),
            @xml.query('
                    for $e in error
                    return
                        if($e is /error[last()])
                            then string($e/description2[1])
                        else concat($e/description2[1], ", ")
                    ')
       ) As ParsedString

SQLFiddle Demo

brief explanation :

The XQuery simply iterate through error elements and return child element description2 only if current error element is the last error element. Otherwise, return description2 value concatenated with comma.