没有 XML 编码的 SQLXML? [英] SQLXML without XML encoding?
问题描述
我使用通用系统进行报告,该系统从数据库视图 (SQL Server 2005) 中获取数据.在这个视图中,我不得不将一对多关系中的数据合并成一行,并使用了 priyanka.sarkar<描述的解决方案/a> 在此线程中:将子查询中的多个结果组合成一个逗号分隔值.解决方案使用 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
除了我的数据现在通过 SQLXML 获得 XML 编码(& => &
等)之外,它完美地工作(它的性能甚至没有那么重) - 我不想要 XML数据毕竟,我只是把它用作一个技巧 - 由于通用系统,我无法围绕它编码来清理它,因此编码数据直接进入报告.我无法在通用系统中使用存储过程,因此此处不可以选择 CURSOR-merging 或 COALESCE-ing...
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...
所以我要寻找的是 T-SQL 中的一种方式,它可以让我再次解码 XML,甚至更好:避免 SQLXML 对其进行编码.显然我可以编写一个存储函数来执行此操作,但我更喜欢内置的、更安全的方式...
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...
感谢您的帮助...
推荐答案
如果将 type
指定为 for xml
的选项,则可以使用 XPath 查询进行转换XML 类型返回到 varchar
.使用示例表变量:
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)
这将打印:
Joel & Jeff, <<BIN LADEN>>
&&BUSH&&
这里有一个没有 XML 转换的替代方法.它确实具有递归查询,因此性能里程可能会有所不同.它来自 Quassnoi 的博客:
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)
这篇关于没有 XML 编码的 SQLXML?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!