CODEWITHSUNDEEP
sql-serverxmlescapingcharacter
Pete
Pete

Reputation: 45

How to insert XML into SQL Server when it contains escaped invalid characters

I'm trying to insert some XML into a SQL Server database table which uses column type XML.

This works fine most of the time, but one user submitted some XML with the character with hex value 3, and SQL Server gave the error "hexadecimal value 0x03, is an invalid character."

Now I want to check, and remove, any invalid XML characters before doing the insert, and there are various articles suggesting how invalid XML characters can be replaced using regex or something similar.

However, the problem for me is that the user submitted the XML document with the invalid character escaped i.e. "", and none of the methods I've found will detect this. This is also why the error was not detected earlier: it's only when inserting it into the SQL database that the problem occurs.

Has anyone written a function that will check for all escaped invalid XML characters? I suppose the character above could have been written as  or , or lots of other ways, so it's quite hard to catch them all.

Thanks in advance for any help you can offer.

Upvotes: 0

Views: 669

Answers (1)

SQLBadPanda
SQLBadPanda

Reputation: 635

You could try importing the XML to a temporary varchar(max) variable or table column and use REPLACE to strip out the offending characters, then insert the cleansed string into the destination CASTing it to XML

Upvotes: 0

Related Questions