topic Re: XMLTYPE Data_type Column Transfer from Oracle to Oracle table in Talend Studio

XMLTYPE Data_type Column Transfer from Oracle to Oracle table

Anonymous — Fri, 03 Nov 2017 11:35:44 GMT

Hi all,

In my table have one column datatype XMLTYPE and data like this <row id='12' xml:space='pre'><c1>12</c1><c2>23</c2><c3>34</c3><c4>45</c4><c5>56</c5></row>. How can i transfer the data from Oracle to Oracle table.

Design:
toracleInput --> tFileOutputDelimeter.

Re: XMLTYPE Data_type Column Transfer from Oracle to Oracle table

Anonymous — Fri, 03 Nov 2017 11:57:21 GMT

Hi there,

You can do this using a tExtractXMLField, which in the DI component palette can be found under Processing/Fields.

This will allow you to extract the data from your single XML column as individual columns, which can then be inserted into your destination database table.

There is a bit of a learning curve for this type of component, but you'll find full details and examples of how to use it in the documentation here:

https://help.talend.com/reader/iYcvdknuprDzYycT3WRU8w/Pc9RZi6IiWw1Kb~Jf0bE0Q

Regards,

Chris

Re: XMLTYPE Data_type Column Transfer from Oracle to Oracle table

Anonymous — Fri, 03 Nov 2017 12:24:49 GMT

Thanks for your answer.

But i don't want to extract the XML data. I want to insert as XMLTYPE column in target table.

Kindly help me.

Re: XMLTYPE Data_type Column Transfer from Oracle to Oracle table

Anonymous — Fri, 03 Nov 2017 12:38:19 GMT

In which case, you just need to specify the "DB Type" for this column as "XMLTYPE" in the output schema of your tOracleOutput (or similar) component.

Re: XMLTYPE Data_type Column Transfer from Oracle to Oracle table

Anonymous — Sat, 04 Nov 2017 04:30:20 GMT

Hi,

I tried that "DB Type" column as "XMLTYPE" in the output schema. But i am getting below error

caused by: ORA-06553: PLS-306: wrong number or types of arguments in call to 'XMLTYPE'.