Geek Answers Handbook

How to make XML values ​​separated by commas using XPath, XQuery in SQL Server

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 highlight description values2 with commas

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

Two problems here.

  • I cannot run the value function in the query above.
  • Using the query, I get values ​​concatenated without a space or comma. So I need to add a space or comma to the concatenated values.
+4
source share
2 answers

SQL Server does not implement the xPath function string-join, so you will need to adopt a two-step process, the first is to extract all terms into strings 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 strings:

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

, FOR XML PATH(''), TYPE , , STUFF :

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;


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;
+6

XQuery for if..else , :

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

:

XQuery error description2, error error. description2, .

+2

More articles:


All Articles