如何获取 sql server XML 列的 xml 安全版本 [英] How to get the xml-safe version of an sql server XML Column

问题描述

有没有办法在 sql server 中获取 xml 列的 xml 安全版本?

Is there a way to get the xml-safe version of an xml column in sql server ?

xml-Safe 我的意思是转义特殊字符，如 <、>、'、& 等

By xml-Safe i mean escaping special characters like <,>,', &, etc.

我想避免自己进行替换.sql server 中是否有内置函数.

I'd like to avoid doing the replacements myself. Is there a build in function in sql server.

我想要实现的是将xml内容存储到另一个xml属性中.

What I want to achieve is to store the xml content into another xml attribute.

推荐答案

我认为 xml-safe 是指对 XML 特殊标签的转义.如果您希望将一个 XML 列包含在另一个 XML 文档中，那么您有两个选择:

I assume that by xml-safe you mean escaping of XML special tags. If you have an XML column you wish to include in another XML document then you have two options:

• project the column as [*]: select ..., xmlcolumn as [*], ... from ... for xml path... this将在结果 XMl 中嵌入列的 XML 内容.例如.如果该列具有值 value 那么结果将类似于 value</row>.
• 将列投影为列名:select ..., xmlcolumn, ... from ... for xml path... 这会将列的内容作为值插入(即它会逃脱它).例如.与上面相同的值将产生 <element><value</element>.立>

• project the column as [*]: select ..., xmlcolumn as [*], ... from ... for xml path... this will embed the XML content of the column in the result XMl. Eg. if the column has the value <element>value</element> then the result will be like <root><row><element>value</element></row></root>.
• project the column as the column name: select ..., xmlcolumn, ... from ... for xml path... this will insert the content of the column as a value (ie. it will escape it). Eg. the same value as above will produce <root><row><xmlcolumn>&lt;element&gt;&lt;value&lt;/element&gt;.

如果您的问题是关于其他问题，那么您将不得不以适当的方式重新表述并正确使用术语.不要发明只有你自己才能理解的新术语.

If your question is about something else, then you're going to have to rephrase it in a proper manner and use terms correctly. Don't invent new terms no one understands but you.

更新:

如果您将 XML 值插入到列中，那么您根本不需要做任何事情.客户端库知道如何处理正确的转义.只要您正确编写代码.请记住，XML NOT 是一个字符串，永远不应该被视为一个字符串.如果您在客户端中编写 XML，请使用适当的 XML 库 (XmlWriter、XML DOM、Linq to XML 等).将 XML 传入 SQL Server 时，使用适当的类型:SqlXml.存储过程应使用适当的参数类型:XML.阅读时，使用适当的方法阅读XML:GetSqlXml().在其中一位设计师(LINQ to SQL、EF 等)中声明类型也是如此.最终，永远不需要手动转义 XML 字符.如果您发现自己这样做，则说明您使用了错误的 API，您必须重新开始.

If you are inserting XML values into the column, then you don't have to do anything at all. The client libraries know how to handle the proper escaping. As long as you write your code correctly. Remeber, XML is NOT a string and should never, ever be treated as one. If you write XML in your client, use an appropriate XML library (XmlWriter, XML DOM, Linq to XML etc). when passing in the XML into SQL Server, use the appropiate type: SqlXml. Stored procedures should use the appropiate parameter type: XML. When you read it, use the appropriate method to read XML: GetSqlXml(). Same goes for declaring the type in one of the miriad designers (LINQ to SQL , EF etc). Ultimately, there is never any need to escape XML characters manually. If you find yourself doing that, you're using the wrong API and you have to go back to the drawing board.

一个好的开始阅读是 Microsoft SQL 中的 XML 支持服务器 2005.

A good start reading is XML Support in Microsoft SQL Server 2005.

最后，要按照您的描述操作 XML(用表 B 的 XML 列更新表 A 的 XML 列)，您可以使用 XML 方法，特别是修改(...插入...)，然后使用 sql:column:

And finally, to manipulate XML as you describe (update XML column of table A with XML column of table B), you use XML methods, specifically modify (... insert...), and you bind the table B column inside the XQuery using sql:column:

update A
set somecolumn.modify('insert {sql:column("B.othercolumn")} before somenode')
from A join B on ...;

在您的评论中，您威胁 XML 作为字符串，正如我已经说过的，您永远不应该这样做:字符串和 XML 就像水和油.

In you comment you threat XML as a string and, as I already said, you should never ever do that: strings and XML are as water and oil.

这篇关于如何获取 sql server XML 列的 xml 安全版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！