XSL Transformation To Split XML Records in [OSB]

Recently I had a scenario where I was selecting more than 10000 records from a Database Table say X. And I had to transform it to the request of a particular SMS Gateway API Operation. The SMS gateway unfortunately can restraint 1000 records in an instance at 10 parallel threads. So my first activity was to Split 10000 records into 1000 records. Since it could have been achievable if I were using DATABASE ADAPTER polling feature where I could define MaxRaiseSize (number of rows per xml document ), I was using the select operation hence I couldn't leverage the MaxRaiseSize property. Anyways, all in all, I had to split my xml records into chunks of 1000 and here I chose XSLT since I researched it to be the fastest in compatible to Oracle Service Bus.

Here is the PROBLEM.

Input:

<?xml version="1.0" encoding="UTF-8" ?>
<ActionTableCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/pcbpel/adapter/db/top/QueryPendingSMSData file:/C:/JDeveloper/mywork/Application1/Scheduler/xsd/QueryPendingSMSData_table.xsd" xmlns="http://xmlns.oracle.com/pcbpel/adapter/db/top/QueryPendingSMSData">
   <ActionTable>
      <recordid>10</recordid>
      <msgid>1</msgid>
   </ActionTable>
   <ActionTable>
      <recordid>13</recordid>
      <msgid>2</msgid>
   </ActionTable>
   <ActionTable>
      <recordid>16</recordid>
      <msgid>3</msgid>
   </ActionTable>
   <ActionTable>
      <recordid>8</recordid>
      <msgid>4</msgid>
   </ActionTable>
   <ActionTable>
      <recordid>9</recordid>
      <msgid>5</msgid>
   </ActionTable>
.
.
.
.
.
   <ActionTable>
      <recordid>19</recordid>
      <msgid>9999</msgid>
   </ActionTable>
   <ActionTable>
      <recordid>36</recordid>
      <msgid>10000</msgid>
   </ActionTable>
</ActionTableCollection>


Expected Output

<tns:getSmsStatusList xmlns:tns="http://webservice.sendSMS.***.com/">
 <SmsStatusRequest>
  <messageId>1</messageId>
  <messageId>2</messageId>
  <messageId>3</messageId>
.
.
.
<messageId>1000</messageId>
 </SmsStatusRequest>

 <SmsStatusRequest>
  <messageId>1001</messageId>
  <messageId>1002</messageId>
  <messageId>1003</messageId>
.
.
.
<messageId>2000</messageId>
 </SmsStatusRequest>
.
.
.
 <SmsStatusRequest>
.
.
.
  <messageId>10000</messageId>
 </SmsStatusRequest>

</tns:getSmsStatusList>




So
Here is the XSLT I used.








<?xml version="1.0" encoding="UTF-8" ?>

<xsl:stylesheet version="1.0"
                xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"
                xmlns:bpws="http://schemas.xmlsoap.org/ws/2003/03/business-process/"
                xmlns:aia="http://www.oracle.com/XSL/Transform/java/oracle.apps.aia.core.xpath.AIAFunctions"
                xmlns:mhdr="http://www.oracle.com/XSL/Transform/java/oracle.tip.mediator.service.common.functions.MediatorExtnFunction"
                xmlns:bpel="http://docs.oasis-open.org/wsbpel/2.0/process/executable"
                xmlns:oraext="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:dvm="http://www.oracle.com/XSL/Transform/java/oracle.tip.dvm.LookupValue"
                xmlns:hwf="http://xmlns.oracle.com/bpel/workflow/xpath"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:med="http://schemas.oracle.com/mediator/xpath"
                xmlns:ids="http://xmlns.oracle.com/bpel/services/IdentityService/xpath"
                xmlns:bpm="http://xmlns.oracle.com/bpmn20/extensions"
                xmlns:xdk="http://schemas.oracle.com/bpel/extension/xpath/function/xdk"
                xmlns:xref="http://www.oracle.com/XSL/Transform/java/oracle.tip.xref.xpath.XRefXPathFunctions"
                xmlns:tns="http://webservice.sendSMS.***.com/"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:ora="http://schemas.oracle.com/xpath/extension"
                xmlns:socket="http://www.oracle.com/XSL/Transform/java/oracle.tip.adapter.socket.ProtocolTranslator"
                xmlns:ns0="http://xmlns.oracle.com/pcbpel/adapter/db/top/QueryPendingSMSData"
                xmlns:ldap="http://schemas.oracle.com/xpath/extension/ldap"
                exclude-result-prefixes="xsi xsl xsd ns0 tns xp20 bpws aia mhdr bpel oraext dvm hwf med ids bpm xdk xref ora socket ldap">

 <xsl:param name="pGroupSize" select="3"/>
 <xsl:template match="node()|@*">
  <xsl:copy>
   <xsl:apply-templates select="node()|@*"/>
  </xsl:copy>
 </xsl:template>
 <xsl:template match="/*">
  <tns:getSmsStatusList>
   <xsl:apply-templates select="ns0:ActionTable[position() mod $pGroupSize = 1]"/>
  </tns:getSmsStatusList>
 </xsl:template>
 <xsl:template match="ns0:ActionTable">
  <SmsStatusRequest>
   <xsl:for-each select=".|following-sibling::*[not(position() > $pGroupSize -1)]">
    <messageId>
     <xsl:value-of select="ns0:msgid"/>
    </messageId>
   </xsl:for-each>
  </SmsStatusRequest>
 </xsl:template>
</xsl:stylesheet>


The technique here is not any grouping technique and not the use for for each group by which I found in most of the blogs in the internet. The technique here is the use of mod operator with position() and the following-sibling:: utilities. Basic idea is to get the xml records N1, N2, N3.. whose positions are mod of (number of records to which we want to split let us say  pGroupSize) and then to get all the XML records which exists before Ni which are its siblings.

I hope you would like this technique though the splitting can be achieved by for-each-group by position, unfortunately for-each-group was not supported in OSB.