BizTalk Training – Mapping – How to implement multi-level Muenchian grouping in BizTalk Maps

I already talked about Muenchian Grouping in BizTalk Maps in the past:

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

Inspired by a question in BizTalk Server Forums: XSLT Mapping Question (Summing Values)… I decided to solve this problem and publish this sample to help this user… what can I say… I simply love mapping problems!!!

So how can we perform multi-level Muenchian grouping in BizTalk Maps and perform some mathematical operations on this group?

You can read more about the problem in the thread, nut basically the problem is: To sum all records and create a unique record for records having the same account type and city!

Solution

Add two scripting functoids to the map:

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

  <xsl:key name="groups" match="Record" use="concat(City, '|', AccountType)"/>

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

  <xsl:for-each select="Record[generate-id(.)=generate-id(key('groups',concat(City, '|', AccountType)))]">
     <Record>
        <xsl:variable name="city" select="City/text()" />
        <xsl:variable name="type" select="AccountType/text()" />
  
        <AccountType>
           <xsl:value-of select="$type" />
        </AccountType>
        <City>
           <xsl:value-of select="$city" />
        </City>
  
        <xsl:variable name="negativeTotal" select="sum(//Record[(City = $city) and (AccountType = $type) and (Sign = '-')]/Price)" />
        <xsl:variable name="positiveTotal" select="sum(//Record[(City = $city) and (AccountType = $type) and (Sign = '+')]/Price)" />
        <xsl:choose>
           <xsl:when test="$positiveTotal &gt; $negativeTotal">
              <Sign>+</Sign>
              <Price>
                 <xsl:value-of select="$positiveTotal - $negativeTotal" />
              </Price>
           </xsl:when>
           <xsl:otherwise>
              <Sign>-</Sign>
              <Price>
                 <xsl:value-of select="$negativeTotal - $positiveTotal" />
              </Price>
           </xsl:otherwise>
        </xsl:choose>
     </Record>
  </xsl:for-each>

• Drag a link from the Second Scripting Functoid to the record “Record” in the destination schema;

You can download the source code from:
How to implement multi-level Muenchian grouping in BizTalk Maps
GitHub

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