SQL Server Insert failure due to XML Schema validation error

I have a XML column in a table and it is defined by 开发者_Go百科a schema. I am trying to insert values into this table by using Insert into tbl1 Select * from tbl for xml. But this is failing due to schema validation failure for one of the records. But i want to insert the records which have passed the validation atleast and i can capture the others later. Can someone help me in this.

SQL server validates all dataset, not single row. If you want to validate Row-by-Row using SQL server tools, methods are:

1. SQLCLR (fastest) link

2. SSIS (easy to create) - using loop FOREACH you try to insert row into table. All failed rows are redirecting to another table.

3. TSQL TRY/CATCH Block - insert xml from single row to schema validated variable. Slowest one.