Skip to Main Content

Parsing XML files stored in MS SQL table to Oracle

User_JD0JESep 29 2021

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

Post Details