Muenchian Grouping and Sorting in BizTalk Maps


Update many years later... Check out this improved approach: http://code.msdn.microsoft.com/Muenchian-Grouping-and-790347d2

First, I'll admit the title of this post isn't entirely accurate, because you can't do the grouping and sorting I'm about to show using the BizTalk mapper.  We'll be overriding the XSLT for a BizTalk map, so therefore it might be more appropriate to call this "Muenchian Grouping and Sorting in XSLT."  But since I'm doing this from a BizTalk perspective, I can live with the subtle difference. 🙂

Let's say I have a flat file schema to import sales order information.  Being a flat file, we can assume that it's completely denormalized, meaning there won't be defined relationships and we'll have redundant data.  Let's take a simplified flat file that looks something like this:

Order222|Item123
Order111|Item456
Order222|Item789

Notice that for each line, we repeat the sales header info (in this case it's just "OrderID," but typically this would include customer info, order date, etc.) again and again.  Our schema ends up looking like this:

source schema

However in our destination schema, we want to group the line data for each header, and we want to sort it, too.  Here's the destination schema we need to use.

destination schema

Sounds like fun, right?  Great!  Let's get started...

What I like to do first is to create a new BizTalk map, and then steal its XSLT to use as a template.  This way we know the namespaces and such are correct.  So I've created a new BizTalk map and added our OrdersFF as the source schema and our Orders schema as the destination.  Right-click on the map and choose "Validate," then steal the output ".xsl" file.  Then click on the functoid grid in the graphical map, and set the Custom XSL Path property to your saved .xsl file.

grid properties

Within the XSLT, we're going to be using a method called the Muenchian Method to handle the grouping and sorting.  This method allows us to group and sort data very quickly (faster than some of the grouping options you can do with the graphical mapper, in fact).

Just below the <xsl:output> block of our XSLT, we're going to create a key value for this grouping.  This creates an index which the transform engine can use to loop through each unique value of that key.

<xsl:key name="groups" match="Order" use="OrderID"/>

From there, at the top of where we're going to start our /Orders branch, we can modify the "for-each" block to loop through our key instead of source nodes, like we might typically do.

<xsl:for-each select="Order[generate-id(.)=generate-id(key('groups',OrderID))]">

If we want to apply sorting, we can add:

<xsl:sort select="OrderID" order="ascending"/>

Finally we loop through the "groups" key we created before and output our data:

<xsl:for-each select="key('groups',OrderID)">

The whole /orders branch of our XSLT now looks something like this:

XSLT with grouping and sorting

And here's the XML output:

xml output

Notice that the orders are now sorted, despite being unsorted in the source file, and the items are now grouped by order.  Nifty, huh?

Sample code:

 

Technorati Tags: ,
Comments (5)
  1. Linda says:

    I did the sample, but failed. I added the xsl code below "<xsl:output …/>

    Additional error information:

    The ‘-‘ character, hexadecimal value 0x2D, cannot begin with a name. Line 6, position 4.

    And I did know the meaning about this sentence,what’s the meaning about "generate-id(.)="?

    <xsl:for-each select="Order[generate-id(.)=generate-id(key(‘groups’,OrderID))]">

    Thanks very much!

  2. eliassal says:

    Chris, lovely article. I faced one issue. Inside VS, testing map gives expected outcome. When I deployed the application, created a recieve location and port (configured map on receive port), also configured a send port which subscribes to the rport, droping the sampl file, I get an ouput file exactly the same as the input which is

    Order222|Item123

    Order111|Item456

    Order222|Item789

    as if map is not applied

    AmI missing something, thankls for your help

  3. Michael says:

    eliassal,

    Did you generate a schema for the flat file and add a custom pipeline with the flat file disassembler component?

  4. Bilal says:

    I have a working solution now and it’s grouping fine. The only thins it that i have to link to an external xsl file in the map.

    Is there a way i can copy and paste the same xsl to the scripting inline xslt functoid and will it work?

  5. Chris Romp says:

    @eliassal: Ditto on @Michael’s question.

    @Bilal: The only way I know of to do this is via an external XSLT.  If you find a way to do this via the inline XSL functoid, I’d love to hear about it!

Comments are closed.

Skip to main content