BizTalk Server 2010: Grouping and Debatching/Splitting Inbound Messages (TypedPolled) from WCF-SQL Adapter
How To Group Messages and Split Them
We receive a Typed Polled message which has details spread across multiple nodes and we want to consolidate the related details to a single node and then debatch / split those messages into a 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 on the map. And to perform grouping, key function and generateId functions are utilized. It is also referred to as a 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 an 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 the incoming message.
<xsl:apply-templates select="s0:TypedPollingResultSet0[generate-id(.)=generate-id(key('group-by-id',s0:OrderID)[1])]" />
If match found then an 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 Batch of messages (here above-mentioned map is used).
The debatching is done using default pipeline and a single message is sent out .
Expression shape named as "Execute Pipeline" has the 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 the message of type AckOut and sends it .
Author
Maheshkumar S. Tiwari
http://tech-findings.blogspot.com/
See Also
The following TechNet Wiki articles on Typed Polling:
- Typed Polling with WCF-SQL Adapter: Best Practices and Troubleshooting Tips (BizTalk 2009)
- ****Typed Polling with WCF-SQL Adapter: Best Practices and Troubleshooting Tips (BizTalk 2010)
- [[BizTalk Virtual Mapper VS Custom-XSLT]]
- XSLT Muenchian Grouping - BizTalk Complex Transformation
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.
Another important place to find a huge amount of TechNet Wiki related articles is Wiki: List of Articles for TechNet Wiki
This article participated in the TechNet Guru for August competition and won the Silver Medal 
.