XPath - Terra Ignota

There are more things in heaven and earth, Horatio,

Than are dreamt of in your philosophy.

As usual before  holidays we've got another issue with timeouts,  adapters errors and database performance.

I found that DB Adapter used several queries and then joins result sets. This way get us right result (very simplified version you can see  bellow) with pretty far from optimal performance. 

<off:OfferActionCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                           xmlns:off="http://xmlns.oracle.com/offer">
  <off:OfferAction>
    <off:id>2121</off:id>
    <off:offerPositionId>
      <off:id>2133</off:id>
      <off:positionNum>3</off:positionNum>
    </off:offerPositionId>
    <off:offerActionDetalCollection>
      <off:OfferActionDetal>
        <off:id>2262</off:id>
      </off:OfferActionDetal>
      <off:OfferActionDetal>
        <off:id>2263</off:id>
      </off:OfferActionDetal>
    </off:offerActionDetalCollection>
  </off:OfferAction>
</off:OfferActionCollection>

Option  'Use Outer Join ..' corrects  query issues , but returns non-unique collection. Like this one:

<off:OfferActionCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                           xmlns:off="http://xmlns.oracle.com/offer">
  <off:OfferAction>
    <off:id>2121</off:id>
    <off:offerPositionId>
      <off:id>2133</off:id>
      <off:positionNum>3</off:positionNum>
    </off:offerPositionId>
    <off:offerActionDetalCollection>
      <off:OfferActionDetal>
        <off:id>2262</off:id>
      </off:OfferActionDetal>
      <off:OfferActionDetal>
        <off:id>2263</off:id>
      </off:OfferActionDetal>
    </off:offerActionDetalCollection>
  </off:OfferAction>

  <off:OfferAction>
    <off:id>2121</off:id>
    <off:offerPositionId>
      <off:id>2133</off:id>
      <off:positionNum>3</off:positionNum>
    </off:offerPositionId>
    <off:offerActionDetalCollection>
      <off:OfferActionDetal>
        <off:id>2262</off:id>
      </off:OfferActionDetal>
      <off:OfferActionDetal>
        <off:id>2263</off:id>
      </off:OfferActionDetal>
    </off:offerActionDetalCollection>
  </off:OfferAction>
</off:OfferActionCollection>

I realised that I don't know much about DB Adapter, so we decide to create XQuery/XPath (or anything else)  to filer collection. 

Well, in fact now I realise that I know about X* almost nothing. By the way, after several hours googling, writing and trying  I found an acceptable message transformation expression.

It loops thru distinct values of OfferAction/id (for clause) and returns first node of Xpath result ( /OfferAction[off:id=$ActionId][1]).

The hardest thing for me was - how to combine XPath filters and expressions.

It works just perfect, but I think that next time I'll play on my own field - 

 queries, indexes and hints.

Happy New Year to everyone! Hope to see you in the next year.