Pivot using BPEL

Purpose

During the past weeks my collegues at Amis (Anton and Lucas) are pivotting using SQL and PL/SQL, so why not also do some pivotting in BPEL, just for the fun of it. The pivotting problem is not so much part of a known business process but an exercise to see how difficult or how easy it is to program a feature in a new language.

Pivotting an XML document here means tranform nodes to attributes and vice versa, as shown in figure 1. The nodes in XML document “receiveInput” are transformed to attributes in XML document “callbackClient”.

Figure 1: Pivot by BPEL

BPEL process

The BPEL process is visualized in figure 2 and 3. Generally does the following:

1. read the input XML
2. iterate over the item nodes (array process)
3. copy the transformed nodes as attributes to the output XML document using a XSL-T stylesheet
4. show the output

Figure 2: BPEL process diagram

Figure 3: BPEL process XML

 <!--
  ///////////////////////////////////////////////////////////////////////////////////////////// 
  // Oracle JDeveloper BPEL Designer 
  // Purpose: Asynchronous BPEL Process
  ///////////////////////////////////////////////////////////////////////////////////////////// 
-->
arrayprocessing" ...
>
  <partnerLinks>
    <partnerLink name="client" partnerLinkType="client:arrayprocessing"
                 myRole="arrayprocessingProvider"
                 partnerRole="arrayprocessingRequester"/>
  </partnerLinks>
  <variables>
    inputVariable"
              messageType="client:arrayprocessingRequestMessage"/>
    <variable name="outputVariable"
              messageType="client:arrayprocessingResponseMessage"/>
  </variables>
  <sequence name="main">
    <receive name="receiveInput" partnerLink="client"
             portType="client:arrayprocessing" operation="initiate"
             variable="inputVariable" createInstance="yes"/>
    <scope name="ArrayProcessScope">
      <variables>
        iterator" type="xsd:integer"/>
        nodeCount" type="xsd:integer"/>
        xpathHelper" type="xsd:string"/>
      </variables>
      <sequence name="Sequence_1">
        <assign name="Initialize">
          <copy>
            <from expression="number(1)"/>
            iterator"/>
          </copy>
          <copy>
            bpws:getVariableData('inputVariable','payload','/ns1:items/ns1:item'))"/>
            <to variable="nodeCount"/>
          </copy>
        </assign>
        bpws:getVariableData('iterator')  <=  bpws:getVariableData('nodeCount')">
          <sequence name="Sequence_2">
            <assign name="Iterate">
              <copy>
                ns1:items/ns1:item[', bpws:getVariableData('iterator'), ']')"/>
                <to variable="xpathHelper"/>
              </copy>
              <copy>
                bpws:getVariableData('iterator')+1"/>
                <to variable="iterator"/>
              </copy>
              <!-- 
                 -  Combination of a transform activity and an append.
              -->
              <bpelx:append>
                <bpelx:from expression="ora:processXSLT('Pivot.xsl', bpws:getVariableData('inputVariable','payload',bpws:getVariableData('xpathHelper')) )"/>
                <bpelx:to variable="outputVariable" part="payload"/>
              bpelx:append>
            </assign>
          </sequence>
        </while>
      </sequence>
    </scope>
    <invoke name="callbackClient" partnerLink="client"
            portType="client:arrayprocessingCallback" operation="onResult"
            inputVariable="outputVariable"/>
  </sequence>
</process>

The heart of the pivot functionality is the transformation as shown in figure 4. I choose to use a XML-T stylesheet instead of creating variables for all nodes, because as node names changes or new nodes are added I do’t have to alter BPEL variables used in the Iterate assign activity, alter the XSL-T and change new message types for input- and output messages should be enough.

The transformation is performed inside the bpelx:append task. Bpelx:append is usually used to append the current row being iterated to the output message. But I do not append the original current row, I append the transformed current row to the output message.

Figure 4: pivot.xsl (namespace definitions ommited for clarity)

<xsl:stylesheet version=”1.0″ xmlns….
<xsl:template match=”/”>
<ns1:row>
<xsl:attribute name=”amount”>
<xsl:value-of select=”/*/ns1:amount”/>
xsl:attribute>
<xsl:attribute name=”name”>
<xsl:value-of select=”/*/ns1:name”/>
xsl:attribute>
ns1:row>
</xsl:template>
</xsl:stylesheet>

I am not sure why  the select must  be “/*/ns1:amount” resp. “/*/ns1:name”. Because the root node is “ns1:item”, “/ns1:name” resp. “/ns1:amount” should be enough. But the BPEL engine needed the wildcard.

Summary / Wrap up

Pivotting an XML document using BPEL can be done. It takes a lot of time to do it 1st time and it resulted in some oddities, but who’s counting.