Error: Illegal XML character

I have a column with a text data type , but its an xml field.When i try to execute a simple statement like

SELECT columnname,
    开发者_运维知识库   CONVERT(xml,coloumnname) 
  FROM employee

This is running fine in QA since it has small amount to data. When i am running in the prod then the exception shoots up. Is there a way so that it can bypasses all the records which has an illegal xml character so that i can execute the code successfully. Using sql sever 2005/2008.

do anyone have a scrip for this?

In all seriousness, you should clean your data. The set of legal XML characters is specified exactly here: http://www.w3.org/TR/xml/#charsets.

To have any illegal character in XML is bad news. Instead to trying to "skip over" this data you need to address your root problem which is bad data.

Can you write a script to clean the data in the database?

I suppose it is possible for your live application to read the data and preprocess it, looking for bad characters before you call CONVERT, but this doesn't address the root problem and is not efficient.

Validate data on-the-fly is slow enough.

1. If you really do not want to use bad data, you should add column to table (e.g. "Is_Validated").

2. Then run script which will check data and if it is converted to xml without exception, update column as 1 else 0.

3. When you run your statement, you should limit converting to xml where Is_Validated=1 only.

4. When you insert new row, check if is valid XML (using TRY/CATCH logic or CLR) and insert only valid rows.

5. When you logic be stable you can begin to validate your bad data.

Update:

Because of script from #2 should be run only one time, it can be simple TSQL: Assuming you have primary key Id as int and xml column Columnname in Table Employee validated by MySchema

 Declare @id int=0,@xml XML(MySchema)
WHILE EXISTS(SELECT * FROM Employee WHERE Id>@Id)
BEGIN
     SELECT TOP 1 @Id=Id FROM Employee WHERE Id>@Id ORDER BY Id
BEGIN TRY
  SET @xml=(SELECT columnname FROM Employee WHERE id=@Id)
      UPDATE Employee SET Is_Validated=1 WHERE Id=@Id
END TRY
    BEGIN CATCH
        UPDATE Employee SET Is_Validated=0 WHERE Id=@Id
    END CATCH
    END