How to get the xml-safe version of an sql server XML Column

Question

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

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

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

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

Solution 1

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 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.

Update:

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.

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

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

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.

Solution 2

It is not a direct answer to this question but to anyone who tries to xml-escape strings in TSQL, here is a little function I wrote :

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

Solution 3

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

SELECT @String FOR XML PATH('')

e.g.

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

then use @string from there

Solution 4

The contents of an XML column are XML. By definition, that is "XML-safe".

Do you need to include XML from a column in an XML element or attribute of another XML document? Then just save the outer XML as a string in the new document.

View more solutions

Comments

[pdiddy]

I need to take the XML and inserted into an XML attribute. I have table A that contains an XML column and Table B that also contain an XML column. I need to get the XML from the B table and store it in an attribut of the xml content of Table A. I tried to do a "<Test xmlContent="+CONVERT(NVARCHAR(MAX), tableBxmlcontent)+"/>" but this wont work because the tableBxmlContent contains <, > etc ...

[brewmanz]

Nice way to convert oil to water!

[J Bryan Price]

And to get rid of the element wrapper use what @remusrusanu said: SELECT @String AS [*] FOR XML PATH('')

[Bob Kaufman]

Remarkably simple solution to a nasty problem! I have a several hundred line function and only had to add two lines at the bottom to solve my problem.