代码之家 › 专栏 › 技术社区 › Koen

没有XML编码的SQLXML?

sqlxml tsql sql-server sql

Koen · 技术社区 · 14 年前

我正在使用一个从数据库视图(SQLServer2005)获取数据的通用报告系统。在这个视图中,我必须在一行中合并从一个关系到多个关系的数据,并使用下面描述的解决方案 priyanka.sarkar 在这个线程中: Combine multiple results in a subquery into a single comma-separated value . 该解决方案使用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

这非常有效(性能也不那么重),但我的数据现在得到了XML编码(&=>_ & 等等)由sqlxml-我毕竟不想要XML数据,我只是用它作为一个技巧-而且由于通用系统,我不能围绕它进行代码清理,所以编码的数据直接进入报告。我不能将存储过程与通用系统一起使用,因此此处不能选择合并或合并光标…

所以我要寻找的是T-SQL中的一种方式,它允许我再次解码XML,甚至更好:避免使用sql xml对其进行编码。显然,我可以编写一个这样做的存储函数,但我更喜欢一种内置的、更安全的方式…

谢谢你的帮助…

2 回复 | 直到 13 年前

Andomar 14 年前

如果您指定 type 作为一种选择 for xml ,可以使用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's blog :

;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)

dotjoe 13 年前

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