How to get an XML safe version of sql server XML column

Question

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

In xml-Safe, I mean escaping special characters like <,>, ', &, etc.

I would like to avoid the replacements themselves. Is there a built-in function in sql server.

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

Answer 1

I assume that with xml-safe you mean escaping special XML tags. If you have an XML column that you want to include in another XML document, you have two options:

• [*]: select ..., xmlcolumn as [*], ... from ... for xml path..., XML- XMl. . <element>value</element>, <root><row><element>value</element></row></root>.
• : select ..., xmlcolumn, ... from ... for xml path... (.. ). . , , <root><row><xmlcolumn>&lt;element&gt;&lt;value&lt;/element&gt;.

- , . , , .

Update:

XML , . , . . Remeber, XML . XML , XML (XmlWriter, XML DOM, Linq to XML ..). XML SQL Server : SqlXml. : XML. , XML: GetSqlXml(). miriad (LINQ to SQL, EF ..). , XML . , API, .

XML Microsoft SQL Server 2005.

, , XML, ( XML A XML B), XML-, (... insert...), B XQuery sql:column:

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

XML , , : XML - .

Answer 2

, , xml-escape- TSQL, , :

CREATE FUNCTION escapeXml 
(@xml nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
    declare @return nvarchar(4000)
    select @return = 
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(@xml,'&', '&')
                ,'<', '&lt;')
            ,'>', '&gt;')
        ,'"', '&quot;')
    ,'''', '&#39;')

return @return
end
GO

Answer 3

Another easy way for xml to escape a string is to use the following:

SELECT @String FOR XML PATH('')

eg.

DECLARE @Input NVARCHAR(4000) = 'bacon & eggs'
DECLARE @String = (SELECT @Input FOR XML PATH(''))

then use @string from there

Answer 4

The content of the XML column is XML. By definition, it is "XML safe."

Need to include XML from a column in an XML element or attribute of another XML document? Then just save the external XML as a string in a new document.