BizTalk Training – Mapping – Muenchian Grouping and Sorting in BizTalk Maps without losing Map functionalities

One of the most difficult tasks in the BizTalk Mapper is taking a flat list (such as a CSV or a flat SQL result) and grouping it into a header-detail hierarchy (such as an Invoice with multiple Lines).

Usually, developers resort to an external XSLT file, but this “kills” the visual mapper. There is an astonishing post by Chris Romp about Muenchian Grouping and Sorting in BizTalk Maps, but it has one limitation. By creating and configuring a Custom XSL Path, we lose all mapping features—you can no longer see your links or use functoids.

Here is how to use the Muenchian Method inside a Scripting Functoid to keep your map clean and functional.

So how can we use Muenchian Grouping without losing Map features?

My First approach: I was trying to put an Inline XSLT functoid and put all the XSL inside

<xsl:key name="groups" match="Order" use="OrderId"/>
<!-- This will loop through our key ("OrderId") -->
<xsl:for-each select="Order[generate-id(.)=generate-id(key('groups',OrderId))]">
   <!-- And let's do some sorting for good measure... -->
   <xsl:sort select="OrderId" order="ascending"/>
   <Order>
      <OrderId><xsl:value-of select="OrderId/text()" /></OrderId>
      <Items>
         <!-- Another loop... -->
         <xsl:for-each select="key('groups',OrderId)">
            <ItemId><xsl:value-of select="ItemId" /></ItemId>
         </xsl:for-each>
      </Items>
   </Order>
</xsl:for-each>

The problem with that approach is that it gives an error:

• XSLT compile error at (9,8). See InnerException for details. ‘xsl:key’ cannot be a child of the ‘ns0:OutputOrder’ element

So, to avoid this error, we need to separate “<xsl:key name=”groups” match=”Order” use=”OrderId”/>” expression from the rest of the XSL (see the second approach)

Second Approach

Add two scripting functoids to the map>

• In the first, configure it to an Inline XSLT Call Template and put the key expression

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

• In the second, configure to an Inline XSLT and the rest of the XSL

<!-- This will loop through our key ("OrderId") -->
<xsl:for-each select="Order[generate-id(.)=generate-id(key('groups',OrderId))]">
   <!-- And let's do some sorting for good measure... -->
   <xsl:sort select="OrderId" order="ascending"/>
   <Order>
      <OrderId><xsl:value-of select="OrderId/text()" /></OrderId>
      <Items>
         <!-- Another loop... -->
         <xsl:for-each select="key('groups',OrderId)">
            <ItemId><xsl:value-of select="ItemId" /></ItemId>
         </xsl:for-each>
      </Items>
   </Order>
</xsl:for-each>

See Sample 1, map: MapOrder.btm.

How can we improve (a little more) this solution

When leading with large files, speed processing is vital. Classical Muenchian grouping uses generate-id(). Muenchian grouping using generate-id() is slower than using the count() function, and shows the worst scalability. The reason is probably the poor implementation of the generate-id() function. In other words, the count() function performs much better.

So to improve Meunchian a little more, we have to use the count() function instead of generate-id():

<xsl:for-each select="Order[count(. | key('groups',OrderId)[1]) = 1]">

• See Sample 1, map: MapOrder2.btm.

Here are some performance stats that I found (see original post):

The graph view works better:

Don’t sacrifice the BizTalk Mapper’s visual benefits just because you need complex grouping. By leveraging the Muenchian Method inside a Scripting Functoid, you get high-performance transformations without the maintenance headache of external XSLT files.

Download

THIS SOLUTION IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

You can download Muenchian Grouping and Sorting in BizTalk Maps without losing Map functionalities from GitHub here:

Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son. 

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira