Group Aggregation in Biztalk 2004 Maps

I had a scenario where I need to group and aggregate values in a BizTalk 2004 map based on several like fields. I put together a prototype that uses an xslt technique known as “Muenchian Grouping.” The overview is that it uses an xsl:key to identify xml nodes based on specific values. Below is the code that makes up the prototype…

Source xml:

<PurchaseOrders>
<PurchaseOrder>
<From>John</From>
<To>Surrendar</To>
<PONumber>1</PONumber>
<LineItems>
<LineItem>
<PONumber>1</PONumber>
<ItemCode>ScreenCleaner</ItemCode>
<Description>Cleans computer screens</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
<LineItem>
<PONumber>1</PONumber>
<ItemCode>ScreenCleaner</ItemCode>
<Description>Cleans computer screens</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
<LineItem>
<PONumber>1</PONumber>
<ItemCode>ScreenCleaner</ItemCode>
<Description>Cleans computer screens type 2</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
<LineItem>
<PONumber>1</PONumber>
<ItemCode>NetworkCable</ItemCode>
<Description>Computer network cable</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
<LineItem>
<PONumber>1</PONumber>
<ItemCode>ScreenCleaner</ItemCode>
<Description>Cleans computer screens type 2</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
<LineItem>
<PONumber>1</PONumber>
<ItemCode>ScreenCleaner</ItemCode>
<Description>Cleans computer screens type 2</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
</LineItems>
</PurchaseOrder>
<PurchaseOrder>
<From>John</From>
<To>Surrendar</To>
<PONumber>2</PONumber>
<LineItems>
<LineItem>
<PONumber>2</PONumber>
<ItemCode>ScreenCleaner</ItemCode>
<Description>Cleans computer screens</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
<LineItem>
<PONumber>2</PONumber>
<ItemCode>NetworkCable</ItemCode>
<Description>Computer network cable</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
<LineItem>
<PONumber>2</PONumber>
<ItemCode>NetworkCable</ItemCode>
<Description>Computer network cable</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
<LineItem>
<PONumber>2</PONumber>
<ItemCode>NetworkCable</ItemCode>
<Description>Computer network cable</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
<LineItem>
<PONumber>2</PONumber>
<ItemCode>NetworkCable</ItemCode>
<Description>Computer network cable type 2</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
</LineItems>
</PurchaseOrder>
</PurchaseOrders>

A screen-shot of the map is attached to the bottom of this post.

This is the xslt that I am running in a script functiod call-template:

<xsl:template name=”POTransform”>
<xsl:param name=”PONumber” />
<xsl:for-each select=”//LineItem[
generate-id()=generate-id(key(‘line-key’, concat($PONumber,ItemCode,Description))[1])]”>
<xsl:apply-templates select=”current()” />
</xsl:for-each>
</xsl:template>
<xsl:template match=”LineItem”>
<xsl:variable name=”LineItemList” select=”//LineItem[(ItemCode = current()/ItemCode)
and (PONumber = current()/PONumber) and (Description = current()/Description)]” />
<xsl:call-template name=”BuildLineItem”>
<xsl:with-param name=”list” select=”$LineItemList” />
</xsl:call-template>
</xsl:template>
<xsl:template name=”BuildLineItem”>
<xsl:param name=”list” />
<xsl:param name=”PONumberParam” />
<xsl:element name=”LineItem”>
<xsl:element name=”PONumber”>
<xsl:value-of select=”$list/PONumber” />
</xsl:element>
<xsl:element name=”ItemCode”>
<xsl:value-of select=”$list/ItemCode” />
</xsl:element>
<xsl:element name=”Description”>
<xsl:value-of select=”$list/Description” />
</xsl:element>
<xsl:element name=”Price”>
<xsl:value-of select=”$list/Price” />
</xsl:element>
<xsl:element name=”Quantity”>
<xsl:value-of select=”sum($list/Quantity)” />
</xsl:element>
</xsl:element>
</xsl:template>
<xsl:key name=”line-key” match=”LineItem” use=”concat(PONumber,ItemCode,Description)” />

And finally, this is the output xml:

<PurchaseOrders>
<PurchaseOrder>
<From>John</From>
<To>Surrendar</To>
<PONumber>1</PONumber>
<LineItems>
<LineItem>
<PONumber>1</PONumber>
<ItemCode>ScreenCleaner</ItemCode>
<Description>Cleans computer screens</Description>
<Price>1.00</Price>
<Quantity>2</Quantity>
</LineItem>
<LineItem>
<PONumber>1</PONumber>
<ItemCode>ScreenCleaner</ItemCode>
<Description>Cleans computer screens type 2</Description>
<Price>1.00</Price>
<Quantity>3</Quantity>
</LineItem>
<LineItem>
<PONumber>1</PONumber>
<ItemCode>NetworkCable</ItemCode>
<Description>Computer network cable</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
</LineItems>
</PurchaseOrder>
<PurchaseOrder>
<From>John</From>
<To>Surrendar</To>
<PONumber>2</PONumber>
<LineItems>
<LineItem>
<PONumber>2</PONumber>
<ItemCode>ScreenCleaner</ItemCode>
<Description>Cleans computer screens</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
<LineItem>
<PONumber>2</PONumber>
<ItemCode>NetworkCable</ItemCode>
<Description>Computer network cable</Description>
<Price>1.00</Price>
<Quantity>3</Quantity>
</LineItem>
<LineItem>
<PONumber>2</PONumber>
<ItemCode>NetworkCable</ItemCode>
<Description>Computer network cable type 2</Description>
<Price>1.00</Price>
<Quantity>1</Quantity>
</LineItem>
</LineItems>
</PurchaseOrder>
</PurchaseOrders>

One thing to note about this solution is that it does require a parent-child relationship to be present in the actual xml data. Often, this relationship is implied in xml by the nesting structure. In such a case, this solution would not be able to find the proper context for the LineItem nodes that are to be rolled up. This example uses the PONumber node to define the parent-child relationship. Including this data in the source xml data in both the parent and child nodes allows that context to be defined so that all LineItems on the same PO can be aggregated.

Here is a screen-shot of the map I used:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s