SSMS Oracle import using OpenQuery over LinkedServer (Oracle Provider for OLEDB) – XML Parsing: unable to switch encoding

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).

Any suggestions?

LE: Please note that a solution relying on Oracle CONVERT() function (with the corresponding encoding type) doesn’t seem to work.

I found a solution, for everyone to know:

Assuming that we put the xml field in a variable of type varchar(max) and then casting it to a variable of type XML (but not by using the CONVERT function, rather just by simple assignment), in the end the value of the newly created XML variable will be included in an insert query, by casting it back to varchar(max).

DECLARE @id numeric(19, 0)
DECLARE @xmlmsg varchar(max)
SELECT @id = id, @xmlmsg = internal_message 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')

DECLARE @xml xml
SET @xml = @xmlmsg

DECLARE @tsql varchar(8000) 
SELECT @tsql = 'INSERT INTO [datab].[dbo].[tr] (ID, CREATED, UPDATED, COMPANY, INTERNAL_MESSAGE, EXCHANGE_MESSAGE) 
    SELECT id, created, updated, company, ''' + CONVERT(varchar(max), @xml) + ''', exchange_message 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 where id = ' + CONVERT(VARCHAR(max), @id) + ''')'

EXEC (@tsql)

You should note that in such cases, seeing that the XML Document Declaration (e.g. “”) is gone missing after inserting the row is normal, due to the fact that SQL Server strips that part (by design). Find out more here: http://msdn.microsoft.com/en-us/library/ms187107(v=sql.90).aspx