没有XML编码的SQLXML？

发布时间：2020-12-16 07:56:03 所属栏目：百科来源：网络整理

导读：我正在使用通用系统进行报告,从数据库视图(SQL Server 2005)获取数据.在这个视图中,我不得不在一行中从一对多关系中合并数据,并使用 priyanka.sarkar在此线程中描述的解决方案： Combine multiple results in a subquery into a single comma-separated valu

我正在使用通用系统进行报告,从数据库视图(SQL Server 2005)获取数据.在这个视图中,我不得不在一行中从一对多关系中合并数据,并使用 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

这完美的工作(甚至没有那么沉重的表现),除了我的数据现在通过SQLXML获得XML编码(& =& amp; amp; amp; amp;等) – 我不想要XML数据,我只是把它用作一个诡计 – 由于通用系统,我无法对其进行编码以将其清理干净,因此编码数据直接转到报告中.我不能使用通用系统的存储过程,所以CURSOR合并或COALESCE-ing在这里不是一个选项…

所以我正在寻找的是T-SQL中的一种方式,可以让我再次解码XML,甚至更好：避免SQLXML进行编码.显然,我可以编写一个这样做的存储函数,但是我更喜欢内置,更安全的方式…

谢谢你的帮助…

如果将类型指定为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,ROW_NUMBER() OVER (PARTITION BY id ORDER BY name),COUNT(*) OVER (PARTITION BY id)
        FROM    @MyTable
        ),with_concat (id,gc,CAST(name AS VARCHAR(MAX)),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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!