Four Stars

SQL XML Oracle - tOracleInput won't retrieve data

Hello Everyone,

 

I have a very simple query from an Oracle DB aiming at returning a single XML node:

 

"
select xmlelement(\"edition\", edition_id) as xml
from table1
where isbn_unhyphenated = '978311036762'
"

 

I want to write the result of the query in an XML-file. The result should be:

 

<edition>430424</edition>

 

My tOracleInput should return the above result as a document to a tFileOutputXML which ought to save it on my drive then.

 

Yet I get the following error:

tOracleInput error line.PNG

tOracleInput error message.PNG.jpg

 

Below you will find the settings of my components:

 

Job & schema XMLoutput.PNGtOracleInput schema.PNG

 

I use Talend 6.4.1.

DB connectivity issues are out of question - checked and tested. The problem starts happening when I want to save XML as a document.

 

Please advice what am I doing wrong in here.

 

Best!

6 REPLIES
Moderator

Re: SQL XML Oracle - tOracleInput won't retrieve data

Hello,

 

 DB connectivity issues are out of question - checked and tested. The problem starts happening when I want to save XML as a document.


You need  to convert the resulting String to a Document using the tConvertType component.

On tFileOutputXML component, check the 'incoming is a document' box.  

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: SQL XML Oracle - tOracleInput won't retrieve data

Thanks for the answer. Unfortunately it does not help.I got the following error:

 

 

Exception in component tFileOutputXML_1 ([job])
java.lang.NullPointerException
    at [project].[job].tOracleInput_2Process(Books_KupDb_Test.java:1054)
    at [project].[job].runJobInTOS(Books_KupDb_Test.java:1508)
    at [project].[job].main(Books_KupDb_Test.java:1327)

 

 

So I went to basics to check if I get anything from tOracleInput at all. To my surprise I stated the string is empty after I put in an SQL query that resulted in an XML. On the other hand, when I used a simple query returning a single column, the output was correct.

 

The output that I check directly in Oracle SQL Developer is correct - I get the expected XML (as described in the first post).

 

Hence a question arises: Does tOracleInput have any problems with SQL queries, that deliver XMLs?

 

This query delivers nothing when executed via tOracleInput:

 

"
select xmlelement(\"edition\", edition_id) as xml
from [table]
where isbn_unhyphenated = '978311036762'
"

 

The expected output is: <edition>430424</edition>

 

And this one is OK:

 

"

select edition_id
from [table]
where isbn_unhyphenated = '978311036762'

"

Four Stars

Re: SQL XML Oracle - tOracleInput won't retrieve data

Hello Everyone,

 

this one has still not been solvedSmiley Sad Anyone any ideas, please?Smiley Happy

Moderator

Re: SQL XML Oracle - tOracleInput won't retrieve data

Hello,

Could you please post your job setting screenshots into forum?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: SQL XML Oracle - tOracleInput won't retrieve data

Hi Sabrina,

 

here is the complete documentation:

 

 01_Job_overview - there are two test scenarios: save as txt and save as xml (incoming document).

 

The first scenario: save as txt has two subscenarios:

 

a) retrieve via normal SQL

b) retrieve via xmlelement

 

ad a) the query gets executed, everything works perfectly, the result is saved into the set path - this makes me say there are no connection problems;

ad b) no errors messages, a non-empty txt-file is saved (1kb), but there is no visible data in it;

 

The second scenario - save as xml (incoming document) after you had been retrieved as string - transform from string to a document on the way. Again, there are two subscenarios, same as in the first scenario:

 

a) retrieve via normal SQL

b) retrieve via xmlelement

 

ad a) error message

 

Exception in component tFileOutputXML_1 (tmp)
java.lang.NullPointerException
    at submissionchecker_20170720.tmp_0_1.tmp.tOracleInput_1Process(tmp.java:930)
    at submissionchecker_20170720.tmp_0_1.tmp.runJobInTOS(tmp.java:1384)
    at submissionchecker_20170720.tmp_0_1.tmp.main(tmp.java:1203)

 

No file created

 

ad b) there is an error:

 

Exception in component tFileOutputXML_1 (tmp)
java.lang.NullPointerException
    at submissionchecker_20170720.tmp_0_1.tmp.tOracleInput_1Process(tmp.java:930)
    at submissionchecker_20170720.tmp_0_1.tmp.runJobInTOS(tmp.java:1384)
    at submissionchecker_20170720.tmp_0_1.tmp.main(tmp.java:1203)

 

an empty file gets created (understandable...)

 

You have settings of individual components in attachments 02 to 04. 05 and 06 shows you what is Oracle output of the query.

 

Please let me know if an additional info is needed.

 

Best,

Jakub

Seven Stars sgv
Seven Stars

Re: SQL XML Oracle - tOracleInput won't retrieve data

I think there's a problem with XMLType and Oracle ...

If you find a solution, i'm really interested.

You can try to use in advanced setting the option "Convert XMLType to Java Type (juste when extract return a NODE)" . . .
But you have to modify a bit your SQL like :

select extract(xmlelement(\"edition\", COLUMN ), 'edition') as xml from table where ...

And in your schema the dbtype must be XMLTYPE and talend type String ...

 

I try it but fail =(

 

Edit :

PS : If the purpose of your job is to convert database data to XML, there is other way to do this in talend