Load data from Oracle CLOB datatype to XMLTYPE datatype

sureshreddy_i · Post by **sureshreddy_i** » Sat Oct 23, 2010 12:06 am

I have the source column as Oracle CLOB datatype and Target is Oracle XMLTYPE datatype.
Source CLOB column contains XML data which i need to extract and load into Target Oracle column with XMLTYPE datatype using Datastage 8.1 Parallel Job.
I was able to read the source CLOB data using Dynamic RDBMS stage but was not able to load this data into Target Oracle table with XMLTYPE datatype.
Can anyone guide me how to do this ???

eostic · Post by **eostic** » Sat Oct 23, 2010 5:10 am

What error do you get? Are you even able to import the metadata? Exactly which Oracle Stage are you using? I haven't talked to a site who has done this...perhaps others here in the forum have. There is new xml datatype support for various databases inside of the Connectors in 8.5, but again, I haven't encountered it. Please share any details you have.

Ernie

sureshreddy_i · Post by **sureshreddy_i** » Sat Oct 23, 2010 10:02 am

I tried using Dynamic RDBS or OCI stage to load into target Oracle table with XMLTYPE datatype. But the job was aborting. When i imported the table definition for this oracle table, for XMLTYPE datatype, it imported as unknown datatype.

chulett · Post by **chulett** » Sat Oct 23, 2010 10:23 am

It might help if you posted the actual abort message.

XMLTYPE is not supported, at least not directly, except perhaps in the new Connector stage as Ernie mentioned. IMHO, you are better off with a PL/SQL procedure for this and there are lots of resources out there for that, for example here. Or you could try the syntax noted in the link as user-defined sql, if you haven't tried that already.