没有XML编码的SQLXML？
没有XML编码的SQLXML？
提问于 2010-07-08 17:53:50
我使用的是一个通用的报告系统，它从数据库视图(SQL Server2005)获取数据。在这个视图中，我必须将一对多关系中的数据合并到一行中，并在此线程中使用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的方式，甚至更好:避免SQLXML对其进行编码。显然，我可以编写一个存储函数来做这件事，但我更喜欢一种内置的、更安全的方式……
谢谢你的帮助。
回答 2
Stack Overflow用户
回答已采纳
发布于 2010-07-08 18:32:10
如果将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)Stack Overflow用户
发布于 2012-01-14 04:26:15
(
select ...
from t
for xml path(''), type
).value('.', 'nvarchar(max)')页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3202415
复制相关文章
相似问题