SQL XML Oracle - tOracleInput won't retrieve data

[Anonymous]

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:

Below you will find the settings of my components:

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!

[Anonymous]

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

[Anonymous]

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'

"

[Anonymous]

Hello Everyone,

this one has still not been solved Anyone any ideas, please?

[Anonymous]

Hello,

Could you please post your job setting screenshots into forum?

Best regards

Sabrina

[Anonymous]

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

01_Job_overview.PNG
02_tOracleInput_output_settings.PNG
03_tConvertType_settings.PNG
04_tFileOutputXML_settings.PNG
05_oracle_sql_result_view.PNG.jpg

[Anonymous]

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

[Anonymous]

Hi,

any progress on that one? I still have issues with exporting XML data directly from an Oracle SQL database via tOracleInput...

Best,

Jakub