SQLXML 没有 XML 编码?

发布于 2024-09-08 18:56:15 字数 1088 浏览 4 评论 0原文

我正在使用一个通用的报告系统,该系统从数据库视图(SQL Server 2005)获取数据。在这种情况下,我必须将一对多关系中的数据合并到一行中,并使用 priyanka.sarkar< 描述的解决方案/a> 在此线程中: 将子查询中的多个结果合并为单个逗号分隔值 。该解决方案使用 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 数据,我只是用它作为一个技巧 - 并且由于通用系统,我无法围绕它编写代码来清理它,因此编码数据会消失直接看报告。我无法在通用系统中使用存储过程,因此 CURSOR 合并或 COALESCE-ing 在这里不是一个选项...

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

感谢您的帮助...

I'm using a generic system for reporting which takes data from a database view (SQL Server 2005). In this view I had to merge data from one-to-many relations in one row and used the solution described by priyanka.sarkar in this thread: Combine multiple results in a subquery into a single comma-separated value. The solution uses SQLXML for merging the data (subquery):

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

That works perfectly (it's not even that heavy in performance) except my data now gets XML encoded (& => & etc.) by SQLXML -I didn't want XML data after all, I just used this as a trick- and because of the generic system I can't code around this to clean it up so the encoded data goes straight to the report. I can't use stored procedures with the generic system so CURSOR-merging or COALESCE-ing is not an option here...

So what I'm looking for is a manner in T-SQL that lets me decode the XML again, or even better: avoids SQLXML from encoding it. Obviously I could write a stored function that does this, but I'd prefer a built-in, more safe manner...

Thanks for your help...

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

不…忘初心 2024-09-15 18:56:15
(
select ...
from t
for xml path(''), type
).value('.', 'nvarchar(max)')
(
select ...
from t
for xml path(''), type
).value('.', 'nvarchar(max)')
忆梦 2024-09-15 18:56:15

如果将 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 的博客

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

If you specify type as an option to for xml, you can use an XPath query to convert the XML type back to a varchar. With an example table variable:

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&&'

One possible solution is:

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)

This will print:

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

Here's an alternative without XML conversions. It does have a recursive query, so performance mileage may vary. It's from 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)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文