Xpath to use alternate element value if preferred value is nilled

Highlighted
Seven Stars

Xpath to use alternate element value if preferred value is nilled

I'm receiving XML as follows:

<Events xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <EventDetails>
    <AccountingDate>2017-01-01</AccountingDate>
    <PaymentDate>2017-01-31</PaymentDate>
  </EventDetails>
  <EventDetails>
    <PaymentDate>2017-01-31</PaymentDate>
    <AccountingDate>2017-01-01</AccountingDate>
  </EventDetails>
  <EventDetails>
    <AccountingDate>2017-01-01</AccountingDate>
    <PaymentDate xsi:nil="true"/>
  </EventDetails>
</Events>

 

I need to extract the PaymentDate if it exists but otherwise substitute the AccountingDate.

 

Can anyone please help with an Xpath expression that will achieve this for me? (It must be done in the Xpath rather than after the tExtractXMLfield component as the actual parsing logic is externally sourced.)

Tags (1)

Accepted Solutions
Seven Stars

Re: Xpath to use alternate element value if preferred value is nilled

Finally solved it after about 4 hours...

concat(substring(PaymentDate,   1 div    (boolean(PaymentDate) and PaymentDate!='')),
       substring(AccountingDate,1 div not(boolean(PaymentDate) and PaymentDate!='')))

(It also caters for the possibility that PaymentDate doesn't exist at all although in my case it always does.)

 

Now I just need to make it work with three possible dates!


All Replies
Seven Stars

Re: Xpath to use alternate element value if preferred value is nilled

Finally solved it after about 4 hours...

concat(substring(PaymentDate,   1 div    (boolean(PaymentDate) and PaymentDate!='')),
       substring(AccountingDate,1 div not(boolean(PaymentDate) and PaymentDate!='')))

(It also caters for the possibility that PaymentDate doesn't exist at all although in my case it always does.)

 

Now I just need to make it work with three possible dates!

Six Stars

Re: Xpath to use alternate element value if preferred value is nilled

Is that an XPath query ?"concat(substring(PaymentDate,   1 div    (boolean(PaymentDate) and PaymentDate!='')),
       substring(AccountingDate,1 div not(boolean(PaymentDate) and PaymentDate!='')))"

is there any way that we can join multiple fields in a different loop from same XML file ?  to get the desired out put ?

Seven Stars

Re: Xpath to use alternate element value if preferred value is nilled

Yes, it is.

You should post a new question, with example of your XML and the result you want to achieve.

Community Manager

Re: Xpath to use alternate element value if preferred value is nilled

Glad to see you back Adam! Smiley Happy

 

Elisa

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

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now