I am trying to migrate the data from an OracleDB to an SQL Server Express database. I'm doing this by openquerying a linked server (data provider based on Oracle Provider for OLEDB). Everything works fine for the tables that contain simple data types. But when I get to the table that contains an XML column it returns an error:
Msg 9402, Level 16, State 1, Line 24 XML parsing: line 1, character 60, unable to switch the encoding
For that I do...
INSERT INTO [datab].[dbo].[tr] (ID, CREATED, UPDATED, COMPANY, INTERNAL_MESSAGE, EXCHANGE_MESSAGE) SELECT * FROM OPENQUERY(DATAB_ORACLE, 'SELECT id, CAST(created AS DATE) AS created, CAST(updated AS DATE) AS updated, company, XMLSerialize(DOCUMENT internal_message AS CLOB) AS internal_message, exchange_message FROM datab.tra')
I have no clue as to what encoding the Oracle column has, neither for the MSSQL column (probably default).
LE: Please note that a solution relying on Oracle CONVERT() function (with the corresponding encoding type) doesn't seem to work.