SQLXML没有进行XML编码？

Question

我正在使用一个通用的报表系统，它从数据库视图（SQL Server 2005）中获取数据。在此视图中，我不得不在一行中合并来自一对多关系的数据，并使用priyanka.sarkar在这个主题中描述的解决方案：将子查询中的多个结果组合成单个逗号分隔值。该解决方案使用SQLXML来合并数据（子查询）：

SELECT STUFF(
    (    SELECT ', ' + Name 
         FROM MyTable _in 
         WHERE _in.ID = _out.ID 
         FOR XML PATH('')),        -- Output multiple rows as one xml type value,
                                   -- without xml tags
    1, 2, '')      -- STUFF: Replace the comma at the beginning with empty string
FROM MyTable _out 
GROUP BY ID        -- Removes duplicates

这个方法非常完美运行（性能也不会太重），除了我的数据现在被SQLXML编码成XML（& => &，等等）。我并不想要XML数据，只是用这个作为一个技巧。由于通用系统的限制，我无法编写代码来清理它，因此编码数据直接进入报告。我不能使用存储过程来处理这个通用系统，因此无法进行CURSOR合并或COALESCE处理...

所以我正在寻找一种T-SQL方式来解码XML，或者更好的是避免SQLXML对其进行编码。显然，我可以编写一个存储函数来执行此操作，但我更喜欢内置且更安全的方式...

谢谢您的帮助...

- Koen

Answer 1

(
select ...
from t
for xml path(''), type
).value('.', 'nvarchar(max)')

- dotjoe

---

这个“type”指令帮我节约了很多时间。 - dance2die

Answer 2

如果您在for xml中作为选项指定type，则可以使用XPath查询将XML类型转换回varchar。以下是一个示例表变量：

declare @MyTable table (id int, name varchar(50))

insert @MyTable (id, name) select 1, 'Joel & Jeff'
union all select 1, '<<BIN LADEN>>'
union all select 2, '&&BUSH&&'

一种可能的解决方案是：

select  b.txt.query('root').value('.', 'varchar(max)')
from    (
        select  distinct id
        from    @MyTable
        ) a
cross apply
        (
            select  CASE ROW_NUMBER() OVER(ORDER BY id) WHEN 1 THEN '' 
                        ELSE ', ' END + name
        from    @MyTable
        where   id = a.id
        order by 
                id
        for xml path(''), root('root'), type
        ) b(txt)

这将打印出以下内容：

Joel & Jeff, <<BIN LADEN>>
&&BUSH&&

下面是一种不需要进行XML转换的替代方法。它包含了一个递归查询，因此性能可能会有所不同。这个方法来自于Quassnoi的博客：

;WITH   with_stats(id, name, rn, cnt) AS
        (
        SELECT  id, name,
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY name),
                COUNT(*) OVER (PARTITION BY id)
        FROM    @MyTable
        ),
        with_concat (id, name, gc, rn, cnt) AS
        (
        SELECT  id, name,
                CAST(name AS VARCHAR(MAX)), rn, cnt
        FROM    with_stats
        WHERE   rn = 1
        UNION ALL
        SELECT  with_stats.id, with_stats.name,
                CAST(with_concat.gc + ', ' + with_stats.name AS VARCHAR(MAX)),
                with_stats.rn, with_stats.cnt
        FROM    with_concat
        JOIN    with_stats
        ON      with_stats.id = with_concat.id
                AND with_stats.rn = with_concat.rn + 1
        )
SELECT  id, gc
FROM    with_concat
WHERE   rn = cnt
OPTION  (MAXRECURSION 0)

- Andomar

---

谢谢，我可以翻译为“(SELECT ... FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')”简化成“将查询结果以XML格式合并并转换为最大长度的nvarchar类型”。 - Koen

+1 我对CROSS APPLY和ROOT选项感到困惑，所以我将@Koen的评论添加为答案。 - dotjoe