Unable to sync Sql Server tables with an XML datatype column

[SvdSinner]

When trying to Sync a table with an XML column using the SQL server client, and exception is thrown:
An unhandled exception of type 'Dotmim.Sync.SyncException' occurred in System.Private.CoreLib.dll
The xml data type cannot be selected as DISTINCT because it is not comparable.
├─ [SqlInstance].[DatabaseName].[DefaultScope].[InternalProvisionAsync].Provision:TrackingTable, StoredProcedures, Triggers.Overwrite:False.:The xml data type cannot be selected as DISTINCT because it is not comparable.
├── [SqlInstance].[DatabaseName].[DefaultScope].[InternalCreateStoredProceduresAsync].Table:RenewalRequest.Overwrite:False.:The xml data type cannot be selected as DISTINCT because it is not comparable.
├─── [DefaultScope].[InternalCreateStoredProcedureAsync].Table:RenewalRequest.StoredProcedure:SelectChanges.:The xml data type cannot be selected as DISTINCT because it is not comparable.
└──── The xml data type cannot be selected as DISTINCT because it is not comparable.

I'll look into the source code tomorrow to see if I can find a fix. If so, I'll post back and send a PR

[Mimetis]

Because the GetChanges stored procedure (generated) is using a DISTINCT in the SELECT statement, it won't allow to use XML Column.

If you are not explicitly using XQuery in your XML column, and you are just storing an xml value, I would suggest to transform the column into VARCHAR(MAX)

[SvdSinner]

I do need to support XML columns. I've been hunting for the code that makes the GetChanges stored procedure, I'm hoping I can change the query when XML columns are present.

[SvdSinner]

It looks like I can add support for non-comparable columns (xml, text, ntext and maybe more if needed). I've got the code written and it appears to work in very early testing.
Unfortunately, I'm at the end of my day on the last day I'm working this year, so I probably won't get to fully test it or submit it as a pull request until next year.

[SvdSinner]

Pull request submitted. Be aware that I have only tested it on the databases that I care about so additional testing would be prudent. Also, I am not currently using change tracking so I couldn't test that chunk. If you have any questions, let me know.

[SvdSinner]

Pull request #941 submitted to replace previous pull requests

[sojan1]

Hi
Will XML Sync works if we add the xml field with a CAST to VARCHAR(MAX) in the Stored Procedure** and also add the field in BulkType