如何在 SQL Server 中使用 XPath、XQuery 使 XML 值以逗号分隔 [英] How to make XML values comma separated using XPath, XQuery in SQL Server
本文介绍了如何在 SQL Server 中使用 XPath、XQuery 使 XML 值以逗号分隔的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个 XML 列,其中的示例值为
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>
我想选择以逗号分隔的 description2 值
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
这里有两个问题.
- 我无法在上面的查询中运行值函数.
- 使用查询,我得到了没有空格或逗号的值.所以我需要在连接的值上添加一些空格或逗号.
推荐答案
SQL Server 没有实现 xPath 函数 string-join
,所以你需要采用两步过程,第一步是使用 nodes()
;
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
然后您可以添加分隔符并将它们连接起来,使用 FOR XML PATH(''), TYPE
,最后使用 STUFF
删除第一个分隔符:
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;
<小时>
完整的工作示例
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;
这篇关于如何在 SQL Server 中使用 XPath、XQuery 使 XML 值以逗号分隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文