如何在 SQL 2005 上使用 FOR XML PATH 时保留与号 (&)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/270948/
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 to preserve an ampersand (&) while using FOR XML PATH on SQL 2005
提问by Duffy
Are there any tricks for preventing SQL Server from entitizing chars like &, <, and >? I'm trying to output a URL in my XML file but SQL wants to replace any '&' with '&'
是否有任何技巧可以防止 SQL Server 对 &、< 和 > 等字符进行实体化？我试图在我的 XML 文件中输出一个 URL，但 SQL 想用 ' &'替换任何 '& '
Take the following query:
进行以下查询：
SELECT 'http://foosite.com/' + RTRIM(li.imageStore)
+ '/ImageStore.dll?id=' + RTRIM(li.imageID)
+ '&raw=1&rev=' + RTRIM(li.imageVersion) AS imageUrl
FROM ListingImages li
FOR XML PATH ('image'), ROOT ('images'), TYPE
The output I get is like this (&s are entitized):
我得到的输出是这样的（&s 被实体化了）：
<images>
<image>
<imageUrl>http://foosite.com/pics4/ImageStore.dll?id=7E92BA08829F6847&raw=1&rev=0</imageUrl>
</image>
</images>
What I'd like is this (&s are not entitized):
我想要的是这个（&s 没有实体化）：
<images>
<image>
<imageUrl>http://foosite.com/pics4/ImageStore.dll?id=7E92BA08829F6847&raw=1&rev=0</imageUrl>
</image>
</images>
How does one prevent SQL server from entitizing the '&'s into '&'?
如何防止 SQL Server 将 '&' 实体化为 ' &'？
采纳答案by ykaganovich
What SQL Server generates is correct. What you expect to see is not well-formed XML. The reason is that &character signifies the start of an entity reference, such as &. See the XML specificationfor more information.
SQL Server 生成的内容是正确的。您希望看到的不是格式良好的 XML。原因是&字符表示实体引用的开始，例如&。有关更多信息，请参阅XML 规范。
When your XML parser parses this string out of XML, it will understand the &entity references and return the text back in the form you want. So the internal format in the XML file should not cause a problem to you unless you're using a buggy XML parser, or trying to parse it manually (in which case your current parser code is effectively buggy at the moment with respect to the XML specification).
当您的 XML 解析器从 XML 中解析出这个字符串时，它将理解&实体引用并以您想要的形式返回文本。因此，XML 文件中的内部格式应该不会给您带来问题，除非您使用有问题的 XML 解析器，或者尝试手动解析它（在这种情况下，您当前的解析器代码目前就 XML 而言实际上是有问题的）规格）。
回答by Janmonn
There are situations where a person may not want well formed XML - the one I (and perhaps the original poster) encountered was using the For XML Path technique to return a single field list of 'child' items via a recursive query. More information on this technique is here (specifically in the 'The blackbox XML methods' section): Concatenating Row Values in Transact-SQL
在某些情况下，人们可能不想要格式良好的 XML - 我（也许是原始海报）遇到的那个是使用 For XML Path 技术通过递归查询返回“子”项的单个字段列表。此处提供了有关此技术的更多信息（特别是在“黑盒 XML 方法”部分）： 在 Transact-SQL 中连接行值
For my situation, seeing 'H&E' (a pathology stain) transformed into 'well formed XML' was a real disappointment. Fortunately, I found a solution... the following page helped me solve this issue relatively easily and without having re-architect my recursive query or add additional parsing at the presentation level (for this as well for as other/future situations where my child-rows data fields contain reserved XML characters): Handling Special Characters with FOR XML PATH
对于我的情况，看到“H&E”（病理染色）转化为“格式良好的 XML”真是令人失望。幸运的是，我找到了一个解决方案......以下页面帮助我相对轻松地解决了这个问题，而无需重新构建我的递归查询或在表示级别添加额外的解析（为此以及我孩子的其他/未来情况-rows 数据字段包含保留的 XML 字符）：使用 FOR XML PATH 处理特殊字符
EDIT: code below from the referenced blog post.
编辑：下面引用的博客文章中的代码。
select
stuff(
(select ', <' + name + '>'
from sys.databases
where database_id > 4
order by name
for xml path(''), root('MyString'), type
).value('/MyString[1]','varchar(max)')
, 1, 2, '') as namelist;
回答by Siva Sankar Gorantla
Try this....
尝试这个....
select
stuff(
(select ', <' + name + '>'
from sys.databases
where database_id > 4
order by name
for xml path(''), root('MyString'), type
).value('/MyString[1]','varchar(max)')
, 1, 2, '') as namelist;
