Repetition

This section continues implementing the requirements of New Horizons Requirements. The aim is to read an XML document with a booking and insert it in database tables “booking” and “visit”. In section Sending to Database , we did the insert in table “booking”. The inserted values were fetched using XPath expressions. In the previous section Transformation with XSLT, we transformed the booking XML such that the values for each “visit” row are grouped together. In this section, we will insert these rows.

A booking can have multiple destinations, each appearing in their own <destination> element. These elements can be iterated with the <ForEachChildElementPipe>. Within this pipe you include a sender that is applied to each element, in our case a <destination> element. Please do the following to do the inserts in table “visit”:

1. Please open Configuration.xml.

2. Add a <ForEachChildElementPipe> as shown:

   ...
         </SenderPipe>
         <XsltPipe
             name="getDestinations"
             styleSheetName="booking2destinations.xsl"
             getInputFromSessionKey="originalMessage">
           <Forward name="success" path="iterateDestinations"/>
         </XsltPipe>
         <ForEachChildElementPipe
             name="iterateDestinations"
             elementXPathExpression="/destinations/destination">
             <!-- You will add your sender here -->
           <Forward name="success" path="Exit"/>
         </ForEachChildElementPipe>
       </Pipeline>
     </Adapter>
   </Configuration>
   

The <ForEachChildElementPipe> applies the elementXPathExpression to the incoming message and iterates over the result set. In the transformed example booking shown in Transformation with XSLT, there is one match:

<destination>
  <bookingId>1</bookingId>
  <seq>1</seq>
  <hostId>3</hostId>
  <productId>4</productId>
  <startDate>2018-12-27</startDate>
  <endDate>2019-01-02</endDate>
  <price>400.00</price>
</destination>

3. Replace the <!-- You will add your sender here --> line with the following XML:

   ...
   <FixedQuerySender
       name="insertVisitSender"
       query="INSERT INTO visit VALUES(?, ?, ?, ?, ?, ?, ?)"
       datasourceName="jdbc/${instance.name.lc}">
     <Param name="bookingId" xpathExpression="/destination/bookingId" />
     <Param name="seq" xpathExpression="/destination/seq" />
     <Param name="hostId" xpathExpression="/destination/hostId" />
     <Param name="productId" xpathExpression="/destination/productId" />
     <Param name="startDate" xpathExpression="/destination/startDate" />
     <Param name="endDate" xpathExpression="/destination/endDate" />
     <Param name="price" xpathExpression="/destination/price" />
   </FixedQuerySender>
   ...
   

This sender is similar to the sender of section Sending to Database. There is an INSERT query with a question mark for each inserted value. The inserted values are fetched using XPath expressions, which act on the current match of the elementXPathExpression as shown at step 2.