使用“FOR XML PATH”时如何避免字符编码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15643683/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I avoid character encoding when using "FOR XML PATH"?
提问by dangowans
I'm looking to create a comma-separated list of values from a SQL Server 2005 table, just like in JanetOhara's question. I'm using a query similar to the one presented in techdo's answerto the question.
我希望从 SQL Server 2005 表中创建一个逗号分隔的值列表，就像在JanetOhara 的问题中一样。我使用的查询类似于techdo对问题的回答中提出的查询。
Everything is working, except the list of values is getting XML encoded. What should be:
一切正常，除了值列表被 XML 编码。应该是什么：
Sports & Recreation,x >= y
Is instead returning as:
而是返回为：
Sports & Recreation,x <= y
Is there a way to disable the XML character encoding when using "FOR XML" in SQL Server?
在 SQL Server 中使用“FOR XML”时，有没有办法禁用 XML 字符编码？
回答by Aaron Bertrand
You just need to use the right options with FOR XML. Here's one approach that avoids encoding:
您只需要使用正确的选项FOR XML。这是一种避免编码的方法：
USE tempdb;
GO
CREATE TABLE dbo.x(y nvarchar(255));
INSERT dbo.x SELECT 'Sports & Recreation'
UNION ALL SELECT 'x >= y'
UNION ALL SELECT 'blat'
UNION ALL SELECT '';
-- BAD:
SELECT STUFF((SELECT N',' + y
FROM dbo.x
FOR XML PATH(N''))
,1, 1, N'');
-- GOOD:
SELECT STUFF((SELECT N',' + y
FROM dbo.x
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)')
,1, 1, N'');
GO
DROP TABLE dbo.x;
回答by Lin
See this post on Creating concatenated delimited string from a SQL result set and avoid character encoding when using “FOR XML PATH”
请参阅有关从 SQL 结果集创建串联分隔字符串并在使用“FOR XML PATH”时避免字符编码的帖子
An alternate approach would be to rely on concatenation of characters (of course sql is not great with string operations as it is developed to work with set theory)
另一种方法是依赖于字符的连接（当然 sql 对字符串操作不是很好，因为它被开发用于集合论）
USE tempdb;
GO
CREATE TABLE dbo.x ( y NVARCHAR(255) );
INSERT dbo.x
SELECT 'Sports & Recreation'
UNION ALL
SELECT 'x >= y'
UNION ALL
SELECT 'blat'
UNION ALL
SELECT '<hooah>';
DECLARE @delimitedText varchar(max)
SET @delimitedText=''
SELECT @delimitedText += CASE WHEN LEN(@delimitedText) > 0 THEN +','+ y ELSE y END
FROM dbo.x
SELECT @delimitedText
GO
DROP TABLE dbo.x;
GO
