sql - 如何在 SQL Server 中使用 XPath、XQuery 使 XML 值以逗号分隔

Question

我有一个 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>

我想选择逗号分隔的 description2 值

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

这里有两个问题。

1. 我无法在上面的查询中运行值函数。
2. 使用查询，我获得了没有空格或逗号的连接值。所以我需要在连接值上添加一些空格或逗号。

Answer 1

SQL Server 没有实现 xPath 函数 string-join，因此您需要采用两步过程，第一步是使用 nodes( );

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

它以行的形式为您提供值:

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;