Xpath to use alternate element value if preferred value is nilled

[alevy]

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.)

[alevy · Accepted solution]

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!

[Anonymous]

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 ?

[alevy]

Yes, it is.

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

[Anonymous]

Glad to see you back Adam! 

Elisa