Geeks With Blogs

My Visual Identity
This is a Flickr badge showing photos in a set called Personal Favorites. Make your own badge here.

maina donaldson a pragmatist's blog

A recent POC presented me with the following problem:  I receive a PurchaseOrder line item list from SAP, containing order header information in a flattened data structure. The SALES_ORDERS node actually contains line item records (complete with order header fields), which we needed to roll up into order-level records. This would be analagous to a SQL SELECT DISTINCT on the OrderNumber. The goal of this exercise was to find an efficient way to use the mapper tool, and not resort to custom xslt altogether.

Not that easy, as I found out, and the solutions presented are credited in large part to my Magenic colleague Doug Marsh. Thanks Doug!

Solution 1: Source and destination schema are the same

A simple approach if the only task of the map is to SELECT DISTINCT, is to use inline XSLT with a copy-of select, to copy the entire node, and use a preceding-sibling selector to check if the same node is there already. The map simply looks like this:

And the inline xslt in the scriping functoid:
<xsl:for-each select="/*[local-name()='OrderList']/*[local-name()='PurchaseOrder'][not(OrderNumber = preceding-sibling::PurchaseOrder/OrderNumber)]">
    <xsl:copy-of select="."/>

Since there are not additional transformations on the PurchaseOrder node, this functoid in-line approach works well. Any additional transformations on the filtered node would have to be built using xslt, not the mapper.

Solution 2: Source needs to be transformed to destination

This is the real-world scenario, with the SAP source schema and the canonical destination. SALES_ORDERS nodes needed to be manipulated extensively to arrive at the Order-level PurchaseOrder node. Inline xslt was not practical.

The looping functoid is to show explicitly the relationship between SALES_ORDERS and PurchaseOrder nodes. It is optional.

The logic is, again, in a scripting functoid, this time with inline c# script. The script creates a global generic list to keep the Unique Order ID in, and compares the list content with the current node content on each loop.

public System.Collections.Generic.List<int> duplicateList = new System.Collections.Generic.List<int>();
public bool IsDuplicate( int OrderNumber )
     if( duplicateList.Contains( OrderNumber ) )
        return true;
     duplicateList.Add( OrderNumber );
     return false;

If the script functoid returns false (new order number), which is checked by the Logical Equal functoid following it, it will become an output record. This is achieved by connecting the Logical Equal output directly to the destination NODE, effectively filtering the input node-list.

This type of construct can be used to filter any map output, for example here:

Both Logical Equals check for specific values and end in the node-to-be-filtered, yielding something analagous to "SELECT * WHERE DOCCATEGOR = 'J' AND 'DOC_CAT_SD = 'C'"

Obviously there are a few other ways to address filtering, most notably through subscriptions. But if it has to be done within a map, these have proven to work well for me.



Posted on Monday, October 29, 2007 2:29 PM BizTalk and SOA | Back to top

Comments on this post: SELECT DISTINCT in a BizTalk Map

# re: SELECT DISTINCT in a BizTalk Map
Requesting Gravatar...
Really cool solution, you saved a lot of my time.

Left by wirawan on Oct 16, 2009 10:38 AM

# re: SELECT DISTINCT in a BizTalk Map
Requesting Gravatar...
Thank you for this post...saved my day!
Left by Jens on Oct 26, 2009 3:16 AM

# re: SELECT DISTINCT in a BizTalk Map
Requesting Gravatar...
I guess, xslt solution above may be applied only when duplicate elements are in order. But other approach using scripting functoid is smart one. It helped me to quickly address similar requirement. Thanks for the post.
Left by Rajesh Kolla on Jan 26, 2010 1:55 PM

# re: SELECT DISTINCT in a BizTalk Map
Requesting Gravatar...
Great. Your Solution 2 match my requirement.

Thanks lot
Left by Vichu on Feb 09, 2010 8:01 PM

# re: SELECT DISTINCT in a BizTalk Map
Requesting Gravatar...
Left by mrobins123 on May 19, 2010 5:38 PM

Your comment:
 (will show your gravatar)

Copyright © Maina Donaldson | Powered by: