使用“FOR XML PATH”时如何避免字符编码？ [英] How do I avoid character encoding when using "FOR XML PATH"?

问题描述

我希望从SQL Server 2005表格中创建逗号分隔的值列表，就像在 JanetOhara的问题。我正在使用类似于 techdo的答案中提出的查询。

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 answer to the question.

一切正常，除了值列表正在获得XML编码。应该是什么：

Everything is working, except the list of values is getting XML encoded. What should be:

Sports & Recreation,x >= y

相反返回：

Sports & Recreation,x <= y

有没有办法在使用时禁用XML字符编码

Is there a way to disable the XML character encoding when using "FOR XML" in SQL Server?

推荐答案

您只需要使用正确的选项与 FOR XML 。这是一种避免编码的方法：

You just need to use the right options with FOR XML. Here's one approach that avoids encoding:

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

-- bad:
SELECT STUFF((SELECT ',' + y
 FROM dbo.x FOR XML PATH('')), 1, 1, '');

-- good:
SELECT STUFF((SELECT ',' + y
 FROM dbo.x FOR XML PATH, TYPE).value('.[1]',
 'nvarchar(max)'), 1, 1, '');

 GO
 DROP TABLE dbo.x;

这篇关于使用“FOR XML PATH”时如何避免字符编码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！