Forum Stats

  • 3,851,866 Users
  • 2,264,048 Discussions


Parsing XML files stored in MS SQL table to Oracle

User_JD0JE Member Posts: 1 Green Ribbon

Hello everyone,

I am new to ODI. I have a problem with parsing XML files.

The files are stored in MS SQL in column of type varchar(max). I have to parse them and create a table in Oracle with parsed data.

My first step is to transfer xmls to Oracle and convert varchar(max) to XMLTYPE and the to parse the files in Oracle with XMLTABLE. I am using the following code in ODI 12c mapping with Hint "SOURCE" for convert the column type

CONVERT(XML, CONVERT(NVARCHAR(max), replace(varchar(max) column, 'encoding="UTF-8"', 'encoding="UTF-16"'))), but i am receiving the following error:

ORA-01461: can bind a LONG value only for insert into a LONG column

The interesting thing is that i don`t have column of type LONG.

Is there other way to parse XML files from MS SQL to Oracle. Which LKM is appropriate for big volume of xml files (over 1 mln files).