SQL 2005 XML Special/Escaped Characters from System.Xml.XmlDocument.OuterXml

Question

I have some XML that has creating using an XmlDocument object in C#.

I am then inserting the xml data into an XML column in SQL 2005 using the XmlDocument.OuterXml method to get the raw xml.

The problem I have is that the xml contains some special characters. Namely:  This is because the xml is built up from user input from various sources.

When I am performing the insert I get a SQL error message saying XML parsing: illegal xml character.

CREATE TABLE #MyTable (MyColumn XML)
INSERT INTO #MyTable VALUES ('<Element>&#x1F;&#x1C;</Element>')

Msg 9420, Level 16, State 1, Line 2

XML parsing: line 1, character 15, illegal xml character

Can someone point me in a direction as to how I can solve this?

It will be extremely hard for me to change anything on the XmlDocument object or original xml itself, however, I can change the mechanism I use to get at the RAW XML. I can also change my database settings if required.

Answer 1

If you've got an XmlDocument with invalid characters in, you basically haven't got valid XML. There's no way of representing anything below U+0020 other than tab, carriage return and linefeed in XML. I'm not entirely sure why, but it's invalid XML according to the specification.

You could store the raw text of the invalid XML in the database, but I assume there's some reason why you wanted it to be an XML column in the first place :)

Do you actually want to preserve this invalid XML? Do you know where it comes from and whether it's required? I'd personally try to cleanse the data wherever possible.

(Btw, the first Stack Overflow data dump had a very similar problem, so it's not just you :)

Answer 2

This is possible INSERT INTO #MyTable VALUES ('')

I add spaces now for clarity

INSERT INTO #MyTable VALUES (' < E l e m e n t > & a m p ; # x 1 F ; < / E l e m e n t > ')