Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Parsing XML files stored in MS SQL table to Oracle

Ivan GanchevSep 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).
BR,
IG.

Comments
Post Details