BizTalk Server 2010: Grouping and Debatching/Splitting Inbound Messages (TypedPolled) from WCF-SQL Adapter

BizTalk Server 2010: Grouping and Debatching/Splitting Inbound Messages (TypedPolled) from WCF-SQL Adapter



How To Group Messages and Split Them 

We receive Typed Polled message which has details spread across multiple nodes and we want to consolidate the related details to single node and then debatch/split those messages into single message.

Input :

Grouping Output (Intermediate):

<?xml version="1.0"?><br>-<ns1:Acks xmlns:ns1="http://orderprocess_schemas.acknowledgement/" xmlns:ns0="http://orderprocess_schemas.ack/">
<ns0:Ack doco="1053">
   <OrderID>DemoId_1</OrderID>
   <OrderDate>2013-07-31T00:00:00Z</OrderDate>
   <RequestedDate>2013-08-15T00:00:00Z</RequestedDate>
   <Currency>INR</Currency>
   <BillTo addressID="Demo Bill Id ">
      <Name>Demo name</Name>
      <City>Demo City</City>
      <State>Demo State</State>
      <PostalCode>410010</PostalCode>
      <Country>India</Country>
   </BillTo>
   <ShipTo addressID="Demo Ship Id ">
      <Name>Demo ship name</Name>
      <City>Demo City</City>
      <State>Demo State</State>
      <PostalCode>25647</PostalCode>
      <Country>India</Country>
   </ShipTo>
   <Item>
      <ItemId>ITM-378910-21</ItemId>
      <Quantity>7</Quantity>
      <UnitOfMeasure>EA</UnitOfMeasure>
      <UnitPrice>5.00</UnitPrice>
      <TaxableAmount>279635.00</TaxableAmount>
      <TaxAmount/>
      <Comment>Demo Item</Comment>
   </Item>
...
...
   <Item>
   <Item>
   <Item>
</ns0:Ack>
<ns0:Ack doco="1051">
   <OrderID>DemoId_2</OrderID>
   <OrderDate>2013-07-31T00:00:00Z</OrderDate>
   <RequestedDate>2013-08-15T00:00:00Z</RequestedDate>
   <Currency>INR</Currency>
   <BillTo addressID="Demo Bill Id">
      <Name>Demo name</Name>
      <City>Demo City</City>
      <State>Demo State</State>
      <PostalCode>410010</PostalCode>
      <Country>India</Country>
   </BillTo>
   <ShipTo addressID="Demo Ship Id">
      <Name>Demo ship name</Name>
      <City>Demo City</City>
      <State>Demo State</State>
      <PostalCode>25647</PostalCode>
      <Country>India</Country>
   </ShipTo>
   <Item>
      <ItemId>ITM-378910-21</ItemId>
      <Quantity>7</Quantity>
      <UnitOfMeasure>EA</UnitOfMeasure>
      <UnitPrice>5.00</UnitPrice>
      <TaxableAmount>279635.00</TaxableAmount>
      <TaxAmount/>
      <Comment>Demo Item</Comment>
   </Item>
   <Item>
      <ItemId>LNV-THkPD-45829173</ItemId>
      <Quantity>3</Quantity>
      <UnitOfMeasure>EA</UnitOfMeasure>
      <UnitPrice>90000.00</UnitPrice>
      <TaxableAmount>279635.00</TaxableAmount><TaxAmount/>
      <Comment>This is comment for line number 2</Comment>
   </Item>
   <Item>
      <ItemId>ITM-378910-21</ItemId>
      <Quantity>2</Quantity>
      <UnitOfMeasure>EA</UnitOfMeasure>
      <UnitPrice>300.00</UnitPrice>
      <TaxableAmount>279635.00</TaxableAmount>
      <TaxAmount/>
      <Comment>This is comment for line number 3</Comment>
   </Item>
   <Item>
      <ItemId>LNV-THkPD-45829173</ItemId>
      <Quantity>1</Quantity>
      <UnitOfMeasure>EA</UnitOfMeasure>
      <UnitPrice>9000.00</UnitPrice>
      <TaxableAmount>279635.00</TaxableAmount>
      <TaxAmount/>
      <Comment>This is comment for line number 4</Comment>
   </Item>
</ns0:Ack>
</ns1:Acks>

Debatched Output: (Final)

Split Message 1:



Split Message 2:


What we need to achieve

Step 1: Map which will group the message

The source to map will be the TypedPolled Data and destination will be referring Envelope schema in which single messages are wrapped.

Custom XSLT is used in the map. And to perform grouping, help of key function and generateId function is utilized. It is also referred as Muenchian method.

To include and apply Custom XSLT, the location path is to be provided and for that we click on the Map grid and go to properties tab. In properties tab the path is provided against the property "Custom XSLT Path"

In it we group all the Item records coming as a individual but having same Order Id. 

In the XSLT we first create a key and initialize it, which holds the uniqueId which is generated and based on incoming first OrderID.

<xsl:key name="group-by-id" match="s0:TypedPollingResultSet0" use="s0:OrderID"/>

And we compare this with rest of the OrderId from incoming message.

<xsl:apply-templates select="s0:TypedPollingResultSet0[generate-id(.)=generate-id(key('group-by-id',s0:OrderID)[1])]" />

If match found then a output node is created with Item records having same OrderID .

<xsl:for-each select="key('group-by-id',s0:OrderID)">

 If not then new uniqueId is created for that node and again checked through all the incoming nodes.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var s0" version="1.0" xmlns:s0="http://schemas.microsoft.com/Sql/2008/05/TypedPolling/Ack" xmlns:ns0="http://OrderProcess_Schemas.Ack" xmlns:ns1="http://OrderProcess_Schemas.Acknowledgement">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
 
  <xsl:key name="group-by-id" match="s0:TypedPollingResultSet0" use="s0:OrderID"/>
 
  <xsl:template match="s0:TypedPolling/s0:TypedPollingResultSet0">
    <ns1:Acks>
      <xsl:apply-templates select="s0:TypedPollingResultSet0[generate-id(.)=generate-id(key('group-by-id',s0:OrderID)[1])]" />
    </ns1:Acks>
  </xsl:template>
 
  <xsl:template match="s0:TypedPollingResultSet0" >
    <xsl:variable name="doco" select="s0:DOCO" />
    <ns0:Ack doco="{$doco}" >
      <xsl:variable name="billid"  select="s0:BilltoID" />
      <xsl:variable name="shipid"  select="s0:ShipToID" />
      <OrderID>
        <xsl:value-of select="s0:OrderID"/>
      </OrderID>
      <OrderDate>
        <xsl:value-of select="s0:OrderDate"/>
      </OrderDate>
      <RequestedDate>
        <xsl:value-of select="s0:RequestDate"/>
      </RequestedDate>
      <Currency>
        <xsl:value-of select="s0:Currency"/>
      </Currency>
      <BillTo addressID="{$billid}">
        <Name>
          <xsl:value-of select="s0:BillToName"/>
        </Name>
        <City>
          <xsl:value-of select="s0:BillToCity"/>
        </City>
        <State>
          <xsl:value-of select="s0:BillToState"/>
        </State>
        <PostalCode>
          <xsl:value-of select="s0:BillToPostal"/>
        </PostalCode>
        <Country>
          <xsl:value-of select="s0:BillToCountry"/>
        </Country>
      </BillTo>
      <ShipTo addressID="{$shipid}">
        <Name>
          <xsl:value-of select="s0:ShipToName"/>
        </Name>
        <City>
          <xsl:value-of select="s0:ShipToCity"/>
        </City>
        <State>
          <xsl:value-of select="s0:ShipToState"/>
        </State>
        <PostalCode>
          <xsl:value-of select="s0:ShipToPostal"/>
        </PostalCode>
        <Country>
          <xsl:value-of select="s0:ShipToCountry"/>
        </Country>
      </ShipTo>
      <xsl:for-each select="key('group-by-id',s0:OrderID)">
        <Item>
          <ItemId>
            <xsl:value-of select="s0:ItemID"/>
          </ItemId>
          <Quantity>
            <xsl:value-of select="s0:Quantity"/>
          </Quantity>
          <UnitOfMeasure>
            <xsl:value-of select="s0:UnitOfMeasure"/>
          </UnitOfMeasure>
          <UnitPrice>
            <xsl:value-of select="s0:UnitPrice"/>
          </UnitPrice>
          <TaxableAmount>
            <xsl:value-of select="s0:TotalAmount"/>
          </TaxableAmount>
          <TaxAmount>
            <xsl:value-of select="s0:TaxAmount"/>
          </TaxAmount>
          <Comment>
            <xsl:value-of select="s0:LineComment"/>
          </Comment>
        </Item>
      </xsl:for-each>
    </ns0:Ack>
  </xsl:template>
</xsl:stylesheet>
 
Step 2: Pass the grouped message through the pipeline (in Orchestration ) which will split the message

Orchestration receives the TypedPolled data and constructs Bacth of messages (here above mentioned map is used).


The debatching is done using default pipeline and single message is sent out .


Expression shape named as "Execute Pipeline" has following line:

GetPipelineOutput = Microsoft.XLANGs.Pipeline.XLANGPipelineManager.ExecuteReceivePipeline(typeof(Microsoft.BizTalk.DefaultPipelines.XMLReceive), BatchAckMsg);

where GetPipelineOutput is a GroupAndDebatchAckScope variable of type: Microsoft.XLANGS.Pipeline.ReceivePipelineOutputMessages and BatchAckMsg is a Message variable which is of type Acknowledgement.xsd(Envelope Schema).

Then we have a loop shape "UntilLastMessage" and its same as while loop, has following line:

GetPipelineOutput.MoveNext()

Next is Construct shape with Message Assignment within it, which has following code:

AckOut = null;
GetPipelineOutput.GetCurrent(AckOut);

It is here where the splitted single message is assigned to AckOut , where AckOut is a Message variable which is of type Ack.xsd(Document Schema)

At last we have Send shape which accepts message of type AckOut and sends it .

Author


Maheshkumar S. Tiwari
iVision Software Pvt Ltd
http://tech-findings.blogspot.com/


See Also


The following TechNet Wiki articles on Typed Polling:
Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.
This article participated in the TechNet Guru for August competition and won the Silver Medal   .
Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Maheshkumar S Tiwari edited Revision 36. Comment: Added text below See Also

  • Maheshkumar S Tiwari edited Revision 32. Comment: Added <hr> around See Also

  • Maheshkumar S Tiwari edited Revision 31. Comment: Added Tag

  • Sandro Pereira edited Revision 27. Comment: fixing small typo error

  • Sandro Pereira edited Revision 16. Comment: Remove spam html code

  • Sandro Pereira edited Revision 15. Comment: fixing TOC

  • Sandro Pereira edited Revision 14. Comment: Format xml code

  • Sandro Pereira edited Revision 12. Comment: Fixing text format, picture format and code format

  • Lex Hegt edited Revision 11. Comment: Cleaned up HTML

  • Lex Hegt edited Revision 10. Comment: Fixed layout

Page 1 of 2 (14 items) 12
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Maheshkumar S Tiwari edited Revision 1. Comment: Added See Also

  • Steef-Jan Wiggers edited Revision 3. Comment: Added resource links

  • Maheshkumar S Tiwari edited Revision 7. Comment: Include description to include Custom XSLT

  • Lex Hegt edited Revision 9. Comment: Minor edit

  • Lex Hegt edited Revision 10. Comment: Fixed layout

  • Lex Hegt edited Revision 11. Comment: Cleaned up HTML

  • Sandro Pereira edited Revision 12. Comment: Fixing text format, picture format and code format

  • Sandro Pereira edited Revision 14. Comment: Format xml code

  • Sandro Pereira edited Revision 15. Comment: fixing TOC

  • Sandro Pereira edited Revision 16. Comment: Remove spam html code

  • Naomi  N edited Revision 20. Comment: Minor edit, added one more link

  • Maheshkumar S Tiwari edited Revision 21. Comment: Added a link to see also

  • Maheshkumar S Tiwari edited Revision 23. Comment: Formatting

  • Sandro Pereira edited Revision 27. Comment: fixing small typo error

  • Sandro Pereira edited Revision 28. Comment: fixing small typo error

Page 1 of 2 (22 items) 12