Storing and XMLType object in Oracle

One Star

Storing and XMLType object in Oracle

Hi,
I am trying to migrate few tables from an Oracle 10g Database to an Oracle 11G Database using Talend. All is fine except for the tables with XMLType fields. To start with Oracle XMLType is parsed as OPAQUE type object in JDBC. This is causing the insert to fail.
Could any one let me know how to resolve this issue an load XMLType data.
(I tried using CLOB but dont know how to modify the insert query dynamically generated by TOS to have "insert into mytable (MyXMLFiled) values (XMLType(?)); and then setClob(myCLOBObject).
One Star

Re: Storing and XMLType object in Oracle

Hi,
Did you try to use the tExtractXMLField to retrieve value of your field, then inserting the value either using XMLType() - and manually creating the XML structure of your field - or XMLElement() in your insert clause ?
i.e. :
INSERT INTO po_xml_tab
VALUES(100, XMLType('<?xml version="1.0"?>
<PO pono="1">
<PNAME>Po_1</PNAME>
<CUSTNAME>John</CUSTNAME>
<SHIPADDR>
<STREET>1033, Main Street</STREET>
<CITY>Sunnyvalue</CITY>
<STATE>CA</STATE>
</SHIPADDR>
</PO>'));
or
INSERT INTO po_xml_tab
SELECT XMLelement("po", value(p))
FROM po p
WHERE p.pono=2001;
XMLElement is generated using DBMS_XMLGEN
Regards,
André
One Star

Re: Storing and XMLType object in Oracle

The issue both with Oracle JDBC drivers and also Talend.
Oracle jdbc drivers dont have a full implementation of XMLType. (I mean there are no getXMLType() or setXMLType() methods).
So to store an XMLType object we need to use CLOB or string (if the length is less than 4K) object and use setObject(Clob/String) and use the XMLType(?) in the prepared statement.
To achieve this this in Talend we need to modify the db_output_bulk.skeleton file.
Steps:
Go to your Talend installation folder and browse to your corresponding db_output_bulk.skeleton file

E.g.: C:\Utilities\TOS-Win32-r31371-V3.2.1\plugins\org.talend.designer.components.localprovider_3.2.1.r31371\components\templates\db_output_bulk.skeleton


Replace line (around 639)

insertValueStmt.append(column.getSqlStmt());
With
if ("XMLTYPE".equals(column.getColumn().getType())) {
insertValueStmt.append("XMLTYPE(").append(column.getSqlStmt()).append(")");
}
else {
insertValueStmt.append(column.getSqlStmt());
}

If Talend is already started, close your jobs, press Ctrl + Shift + F3 then reopen your job and check the generated code. It should now include these changes.
(This solution was provided my colleague Petru.)

We believe this fix should be part of Talend until Oracle JDBC drivers evolve fully Smiley Happy