topic Re: Extract Information from a XML-File which is stored as a row in Oracle in Talend Studio

Extract Information from a XML-File which is stored as a row in Oracle

Anonymous — Sat, 16 Nov 2024 14:10:28 GMT

Dears,

I have a table in oracle DataBase which stores XML file. I want to parse this XML column and populate the output to another table. Can anyone please help me this regard.
Source Table:

CREATE TABLE XML_TAB
(rid NUMBER(5),
xml_data XMLTYPE);

INSERT INTO xml_tab
(rid,
xml_data
)
VALUES (1,
XMLTYPE
('<buzz-company-data>
<company>
<name>AT n T</name>
<industry>Telcom</industry>
<date>6/8/2008</date>
<volume>250</volume>
<cgmtype>blog</cgmtype>
</company>
<company>
<name>Sprint</name>
<industry>Telcom</industry>
<date>6/9/2008</date>
<volume>150</volume>
<cgmtype>forum</cgmtype>
</company>
<company>
<name>Motorola</name>
<industry>Telcom</industry>
<date>6/8/2008</date>
<volume>50</volume>
<cgmtype>blog</cgmtype>
</company>
</buzz-company-data>')
);
COMMIT ;

Target Table :
CREATE TABLE XML_DATA
(
NAME VARCHAR2(100 BYTE),
INDUSTRY VARCHAR2(100 BYTE),
VOLUME NUMBER,
DATE1 DATE,
CGMTYPE VARCHAR2(100 BYTE)
)
Regards,
Ram

Re: Extract Information from a XML-File which is stored as a row in Oracle

Anonymous — Fri, 31 Oct 2008 20:09:08 GMT

Hello,
Your job should looks like
tOracleInput -> tParseXMLRow -> tOracleOutput
Configure your tParseXMLRow to parse the xml_data column with "buzz-company-data/company" as loop.
HTH,

Re: Extract Information from a XML-File which is stored as a row in Oracle

Anonymous — Mon, 03 Nov 2008 04:01:17 GMT

Hi ,
As per your suggestion i tried, but I am getting a Null pointer exception. can you please help me in this regard. I have also attached screen shot for your kind perusal.
Regards,
Ram
Error Message:
Starting job Sunny_XML_Data at 08:55 03/11/2008.
connecting to socket on port 4237
connected
connecting to socket on port 4424
connected
Exception in component tParseXMLRow_1
java.lang.NullPointerException
at java.io.StringReader.<init>(StringReader.java:33)
at test.sunny_xml_data_0_1.Sunny_XML_Data.tOracleInput_1Process(Sunny_XML_Data.java:986)
at test.sunny_xml_data_0_1.Sunny_XML_Data.runJobInTOS(Sunny_XML_Data.java:1450)
at test.sunny_xml_data_0_1.Sunny_XML_Data.main(Sunny_XML_Data.java:1352)
disconnected
disconnected
Job Sunny_XML_Data ended at 08:55 03/11/2008.

Re: Extract Information from a XML-File which is stored as a row in Oracle

Anonymous — Tue, 04 Nov 2008 05:35:03 GMT

Hi Ram,
can you please check the generated code in the following line: Sunny_XML_Data.java: 986
And what is your TOS version?
Did the problem raise with your posted testdata? If so, I could try to reproduce it.
Bye
Volker

Re: Extract Information from a XML-File which is stored as a row in Oracle

Anonymous — Tue, 04 Nov 2008 08:01:35 GMT

Dear Volker,
Thanks for your quick reply.
Code at Sunny_XML_Data.java:986 : .read(new java.io.StringReader(xmlStr_tParseXMLRow_1)).
Version : TOS 2.4.1.r16077(Even In version TOS 3.0 i am getting this same error).
Yes you can try it with the data that i have posted.
Note : when I change the data type in database to CLOB. I am able to extract the data successfully. I am getting this error only for XMLTYPE data type. To my little knowledge in Talend, I think it is not possible to read XMLTYPE data type in oracle using Talend. If you think my statement is wrong kindly correct me. Thanks in advance. I have also attached the screen shot for your kind perusal.
Table Used :
SQL> desc xml_ram
Name Null? Type
----------------------------------------- -------- ---------
XML_DATA CLOB
Table that has been referred in the Below job is : xml_ram.

Re: Extract Information from a XML-File which is stored as a row in Oracle

Anonymous — Sun, 09 Nov 2008 21:31:14 GMT

Hi Ram,
sorry for the late post. I do not have access to oracle to create a testcase and it sounds like a specialty of the oracle data. So I could only try to give some ideas what to check.
The first step I would do is to create a simple job read the column and try to dump it on screen with tLogRow or tJavaRow. Take a look if there are different results, how are null values handled... Then try to use tParseXMLRow again. Is it working in the simple job? Working for some rows? And so on.
Hope this helps.
Bye
Volker

Re: Extract Information from a XML-File which is stored as a row in Oracle

Anonymous — Tue, 11 Nov 2008 13:14:54 GMT

Dear Volker,
sorry for the late response. As per your suggestion I have created a job, but still I am getting the same error message. As I mentioned earlier in my post Talend is not able to read XMLTYPE datatype in oracle. Please correct me if I am wrong. I am not able to attach scrren shot. Thanks in advance.
Regards,
Ram

Re: Extract Information from a XML-File which is stored as a row in Oracle

Anonymous — Wed, 12 Nov 2008 00:19:17 GMT

I have seen similar behavior with other 'special' or opaque datatypes such as TIMESTAMP WITH LOCALTIME. The issue is part Talend and part the Oracle jdbc driver. There are some datatypes that wont return natively without some access to the actual Oracle connection object/classes to use Oracle specific calls (you probably need to change tOracleConnection for that) . One way you may be able to work around this without using the Oracle connection object (It worked for me with TIMESTAMP WITH LOCALTIME) Is to let Oracle database convert the value to a return type that the vanilla sql connection object likes. I would try converting your XMLType to a string or clob,etc in the SQL using the getClobVal(), getStringVal(), getBlobVal(). eg:
SELECT e.getClobVal() FROM xwarehouses e;
see:
http://www.acs.ilstu.edu/docs/oracle/server.101/b10759/ap_examples002.htm