SQL Server 2005: Illegal XML Character

[wilbiew]

I am attempting to create a job which will insert data into a column with an XML datatype. However, the following error occurs:

"XML parsing: line 1, character 170, illegal xml character"

In this particular case, the illegal character is "#x13", but because the data comes from external sources, I must assume that all, if not most, of the illegal XML characters can exist in the data I am trying to insert. Performance is important, so I do not want to use CASE statements to check for every kind of illegal character and REPLACE those characters. I also do not want to maintain some type of look up table with illegal XML characters and use that to replace illegal characters because maintenance should be minimal.

Is there a function that is native to SQL Server 2005 that will "cleanse" illegal XML characters? What about .Net (assuming that I use SQL CLR)?

Basically, I want to know the best way to get around this issue.

[MAK]

import into a text or varbinary column and do the cleaning at a later time using batch job

[wilbiew]

Mak, thanks for your reply.

What would be the best way to cleanse this data? For example, do I have a SELECT statement with multiple CASE statements which REPLACEs all of the known problematic values? Or do I have a look up table that performs a similar function. It is expensive either way, and maintenance will also be problematic.

Please let me know if there is an easier way. The ideal scenario is if Microsoft provides a built-in solution.