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)
1 ACCEPTED SOLUTION

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!

4 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