tInputFileXML returns null values in Open Studio 7.1

Highlighted
Seven Stars

tInputFileXML returns null values in Open Studio 7.1

Hi,

 

Using an xml that I have entered as metadata (I don't know why, but xsd files are not working 100% with the mapping), I have imported it as tInputFileXML in a job. This file is in attachment. This is the structure in the metadata:

 

metadata_example.PNG

In the file I have 2 transactions, and in each transactions I have  2 changeEvents. 2 of the events are of type QualificationInformationChangeEvent; and 2 are customsqualificationValidityChangeEvent.

The issue: when I try to get the 4 records, I get nulls everywhere. This is the loop XPath query that I use:

"/ns0:replicationMessage/body/transaction/changeEvent"

job.PNG

 

and the results:

|messageId|sender|recoveryMode|sentOnTime|transactionId|changeType|changeTime|versionId|source|typeOfQualification...
|=--------+------+------------+----------+-------------+----------+----------+---------+------+-------------------+
|null |null |null |null |null |null |null |null |null |null
|null |null |null |null |null |null |null |null |null |null
|null |null |null |null |null |null |null |null |null |null
|null |null |null |null |null |null |null |null |null |null

 

What went wrong? Any help is welcome.

 

 


Accepted Solutions
Seven Stars

Re: tInputFileXML returns null values in Open Studio 7.1

Hi,

 

I have finally used the wizard to create the metadata.

 

Taking my example file, I have created a new input xml

 

importloopmetadata.png

Then I select only the fields from QualificationInformationChangeEvent.

select fields metadata.png

 

then, in a new job, I dragdropped the metadata as tInputXMLFile, and connected it to a tLogRow.

metadataImported.PNG

 

and it found all the records (2). 

Rahll, thanks for your support. I will open now another ticket with my next issue...

 

 

 


All Replies
Community Manager

Re: tInputFileXML returns null values in Open Studio 7.1

Have you configured your XPaths? Can you show us the configuration of your tFileInputXML?

Seven Stars

Re: tInputFileXML returns null values in Open Studio 7.1

Hi,

 

thanks. Of course, here it is:

xpathquery.PNG

 

 

Community Manager

Re: tInputFileXML returns null values in Open Studio 7.1

I see your loop shows a namespace, but your XPaths do not. Namespaces can be a bit of a pain here. You have a couple of options, you can add your namespaces to your XPaths. This will be a bit of a pain, but means you are being 100% accurate. Alternatively, goto the "Advanced Settings" and click on the "ignore the namespaces". See if that changes anything

Seven Stars

Re: tInputFileXML returns null values in Open Studio 7.1

Hi rahll, Thank you very much for your help. How can I add a namespace in a xpath? Could you show a picture of what you mean? Where should I add it? Thanks for your help and support.
Community Manager

Re: tInputFileXML returns null values in Open Studio 7.1

I'm afraid I do not have any jobs where I have done this on the machine I am currently using. I tend to be lazy and just use the "ignore the namespace" option in most cases :-)

However, this should help you (https://stackoverflow.com/questions/3931817/xpath-expression-from-xml-with-namespace-prefix)

Seven Stars

Re: tInputFileXML returns null values in Open Studio 7.1

Hi rahll,

 

ignoring the namespaces didn't work. Any ideas? I'm loosing hope...

Community Manager

Re: tInputFileXML returns null values in Open Studio 7.1

Can you give an example of your actual XML file (remove any private data) and the XPaths you have just tried please? I will take a look and see what I can suggest

Seven Stars

Re: tInputFileXML returns null values in Open Studio 7.1

Hi rahll,

 

The test I am doing since yesterday is with the file example.xml. The result is exactly the same I get with the real data. If I get to make work example.xml, that's it. The problem is that the file doesn't produce 4 records with data in Talend. It produces 4 records with null values. If I solve the problem with example.xml, I have solved the problem.

 

Any help is welcome.

Seven Stars

Re: tInputFileXML returns null values in Open Studio 7.1

Update:

 

I have created a new job and I have mapped manually some fields. It is working now, but it is going to take me ages to fix all the mapping manually. Here is the mapping:

 

new_mapping.PNG

Community Manager

Re: tInputFileXML returns null values in Open Studio 7.1

Sorry, I didn't see the example XML you posted originally.

 

Your new layout makes sense having seen that. You need to loop on the element that is the key to your row data. My assumption previously was that you had the loop element correct since you were getting the correct number of rows, albeit with nulls. This would normally be a symptom of a namespace issue from my experience. I may have jumped to that conclusion, so I apologise for sending you down the wrong track.

 

I know populating the XPaths like this will be time consuming, but there is no easier way of doing this. I've had to interrogate XML files with multiple nested loops which have required multiple components and hundreds of XPaths, so know it is not fun. 

Seven Stars

Re: tInputFileXML returns null values in Open Studio 7.1

Hi,

 

I have finally used the wizard to create the metadata.

 

Taking my example file, I have created a new input xml

 

importloopmetadata.png

Then I select only the fields from QualificationInformationChangeEvent.

select fields metadata.png

 

then, in a new job, I dragdropped the metadata as tInputXMLFile, and connected it to a tLogRow.

metadataImported.PNG

 

and it found all the records (2). 

Rahll, thanks for your support. I will open now another ticket with my next issue...

 

 

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now