sql - 如何在查询中使用 FOR XML PATH ('' )而不转义特殊字符？

Question

我有这个查询:

SELECT DISTINCT
            f.CourseEventKey,
            (
                SELECT      f.Title + '; ' AS [text()]
                FROM        @Facilities
                WHERE       CourseEventKey = f.CourseEventKey
                ORDER BY    f.Title
                FOR XML PATH('')
            ) Facilities
FROM        @Facilities f

它产生这个结果集:

CourseEventKey Facilities
-------------- -----------------------------------
29             Test Facility 1; 
30             Memphis Training Room; 
32             Drury Inn & Suites Creve Coeur;

数据很好，但是&实际上是编码的&，这不适合我的目的。

如何修改此查询以返回数据中特殊字符的原始值？

Answer 1

使用,TYPE).value('.','NVARCHAR(MAX)')并且您的特殊字符不会被转义:

SELECT DISTINCT
            f.CourseEventKey,
            (
                SELECT      f.Title + '; ' AS [text()]
                FROM        @Facilities
                WHERE       CourseEventKey = f.CourseEventKey
                ORDER BY    f.Title
                FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')
            AS Facilities
FROM        @Facilities f

这归功于Rob Farley .

更新:
我刚刚听说这个新方法。我还没有彻底测试它，希望得到任何反馈。我们可以替换[text()]与 [processing-instruction(x)] ，像这样

select 'hello & there >' as [processing-instruction(x)] FOR XML PATH('')

将返回

<?x hello & there >?>

我们只需要去掉 <? ... ?>