topic Re: Select from ORACLE XMLTYPE Datatype to any ouput. getDocument in Talend Studio

Select from ORACLE XMLTYPE Datatype to any ouput. getDocument

Anonymous — Fri, 17 Oct 2014 19:52:10 GMT

I am trying to move XML data that is stored in an Oracle 11 database as type XMLTYPE to anything else. I would be fine with simply being able to export into a delimited text file. The problem being I do not see where or how to allow Talend to loop thru the XML document as an XML document.
I have ran into a wall trying to process an XMLTYPE Oracle document from Oracle. I can not find anywhere that shows how to parse thru the stored XML document.
I found one post that was directed towards creating the XMLTYPE but nothing about pulling data out.
This is the Oracle schema / DDL

I get this error when trying to perform the following.

Any assistance will be greatly appreciated.

Re: Select from ORACLE XMLTYPE Datatype to any ouput. getDocument

Anonymous — Mon, 20 Oct 2014 05:03:46 GMT

Hi,

I am trying to move XML data that is stored in an Oracle 11 database as type XMLTYPE to anything else. I would be fine with simply being able to export into a delimited text file. The problem being I do not see where or how to allow Talend to loop thru the XML document as an XML document.
I have ran into a wall trying to process an XMLTYPE Oracle document from Oracle. I can not find anywhere that shows how to parse thru the stored XML document.
I found one post that was directed towards creating the XMLTYPE but nothing about pulling data out.
This is the Oracle schema / DDL

The screenshot is missing. Could you please check it?
Best regards
Sabrina

Re: Select from ORACLE XMLTYPE Datatype to any ouput. getDocument

Anonymous — Mon, 20 Oct 2014 12:51:48 GMT

Re: Select from ORACLE XMLTYPE Datatype to any ouput. getDocument

Anonymous — Mon, 20 Oct 2014 12:54:25 GMT

Re: Select from ORACLE XMLTYPE Datatype to any ouput. getDocument

Anonymous — Mon, 27 Oct 2014 15:01:38 GMT

I pasted the screenshots and have not received a response. Can you please help me with my dilemma?
I still have not found a way to retrieve data from an xmltype column in oracle with talend. When using the TORACLE_INPUT component and performing a select *... The query returns a null for the xmltype column. I am trying to extract from oracle to netezza and I am having a horribly difficult time even accessing the xmltype from talend.

Re: Select from ORACLE XMLTYPE Datatype to any ouput. getDocument

Anonymous — Tue, 28 Oct 2014 11:37:04 GMT

hi,
it's just a workaround but try to read your XMLTYPE as a byte array (byte[]) and in a tjavarow create new String with it.


ouput_row.XML_DOCUMENT = new String(input_row.XML_DOCUMENT)

As the error tell us, the type of the object return is a resultSet & it's not trivial to convert it to a Document type.
Did you try tha advanced setting that allow to use the extract() Oracle method (return String type) but remain that there's 4.0000 caracters constraints (i guess) and it seems slower than using byte[]
hope it helps
laurent

Re: Select from ORACLE XMLTYPE Datatype to any ouput. getDocument

Anonymous — Tue, 28 Oct 2014 12:15:27 GMT

Thank you for your response Laurent. I have tried the advanced setting which renders no error however it views the field as a null. When trying to use other java features I am getting null pointer exceptions. I attempted to use your suggestion and I am getting a type mismatch. I have only been working with Talend since August of this year so please forgive me for my lack of knowledge.
I feel that it has to do with how I am trying to access the data. Perhaps Talend is just not capable of accessing this data type?
Here is the DDL of the Oracle table I am trying to pull from along with a snippet of one of the records. I can pull everything but the XML_DOCUMENT field. I am beginning to believe that this is a shortcoming of the tool.

Re: Select from ORACLE XMLTYPE Datatype to any ouput. getDocument

Anonymous — Tue, 28 Oct 2014 14:13:18 GMT

just change javatype to byte[] and create new String in tjavaRow


output_row.GO_XML = new String(input_row.GO_XML);

The last time I've done such things, it was still some binary data in result,I've cut (clean) them to retrieve my data.
regards

Re: Select from ORACLE XMLTYPE Datatype to any ouput. getDocument

Anonymous — Wed, 29 Oct 2014 14:09:40 GMT

Using the code below I can select from one of the XML_DOCUMENTS that does not have multiple nodes. When there are nodes that require the loop I error due to duplicate. Considering that I can select data from individual nodes based on primary key I feel there must be a way that I can use the xpath to loop over these documents some way. Perhaps not...
Any ideas?

SELECT 
extractvalue(XML_DOCUMENT, '/cds-mistint/tt_cds-mistint/store-nbr/text()')  as STORE_NBR,
extractvalue(XML_DOCUMENT, '/cds-mistint/tt_cds-mistint/control-nbr/text()')  as CONTROL_NBR,
extractvalue(XML_DOCUMENT, '/cds-mistint/tt_cds-mistint/line-nbr/text()')  as LINE_NBR,
extractvalue(XML_DOCUMENT, '/cds-mistint/tt_cds-mistint/sales-nbr/text()')  as SALES_NBR
FROM TABLE_NAME
where GUID = 'PRIMARY_KEY'

Re: Select from ORACLE XMLTYPE Datatype to any ouput. getDocument

Anonymous — Wed, 29 Oct 2014 14:38:42 GMT

with something like

/cds-mistint/tt_cds-mistint//store-nbr

you can retrieve all nodes store-nbre under

hope it helps

Re: Select from ORACLE XMLTYPE Datatype to any ouput. getDocument

Anonymous — Fri, 10 Jul 2015 08:38:08 GMT

how to extract xml data from xml type column in database table as input and those xml data need to insert some other tables in database as output please help me to find this