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;