Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
1  
A couple things: (a) use nvarchar(MAX) for XML in a string, and (b) parameterize the second query, as you may have single quote (SQL injection) issues -- this also avoids having to cast the value back to a character type again. –  Jon Seigel Mar 6 '13 at 17:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.