BizTalk Mapper Patterns: How to map values from a repeating node into a single node using conditions

Well, I decided to take a few minutes of my vacation to play a little with … BizTalk, renew some knowledge, answer a few emails, and maybe try to answer some questions on the forums.

This exercise (or pattern) is actually from a question that I found on the forums: Reg BizTalk Mapping, which I found interesting.

So what’s the best way to map some values from a repeating node into a single node base in some conditions?

Note: You can find all the mapping logic of this exercise in the forum. Briefly we have a repeating node TimeSeries, and based on the Path attribute value of TimeSeries node, we will map to some elements of the output message:

• If “Path” attribute == “1” then assign the value of “TimedValue” to “Quantity”
• If “Path” attribute == “2” then assign the value of “TimedValue” to “NRJQuantity”
• If “Path” attribute == “3” then assign the value of “TimedValue” to “AvgCal”
• If “Path” attribute == “4” then assign the value of “TimedValue” to “AvgDens”

First Solution: Using only functoids (without custom XSLT)

To solve this mapping problem using this approach, for each element in the destination schema, we need to drag:

• One Equal functoid and drag a link from the attribute Path in the source schema to this functoid. This will be the first input parameter in the functoid.
  • And in the second parameter, we need to put the number that we want to find, in this case: 1.
• Drag a Value Mapping functoid to the grid
  • Drag a link from the Equal functoid to this Value Mapping functoid.
  • Drag a link from the TimedValue element in the source element to the Value Mapping functoid.
• Drag a link from the Equal functoid for the element in question in the destination schema, in this case, the Quantity element
• And finally, we need to drag a link from the Value Mapping functoid to the respective element in the destination schema, in this case again the Quantity element, as you can see in the picture below.

• We need to repeat the above steps for all the elements until we get the following map:

This solution is correct and, in fact, is what we normally find in this type of mapping problem; however, this is not the best option in terms of performance. If we analyze the XSLT regenerated by the BizTalk mapping engine, by:

• Right-click on the map and select the option Validate Map.
• In the Output windows, press CRTL key and click on the link of The file in the output XSLT is stored in the following file, it will open this file in a new window
• Right-click and select the View Source option.

We will see that for each element in the destination schema, it will be a one-for-each element:

<ns0:Req>
  <xsl:for-each select="TimeSeries">
    <xsl:variable name="var:v1" select="userCSharp:LogicalEq(string(@Path) , '1')" />
    <xsl:if test="$var:v1">
      <xsl:variable name="var:v2" select="string(@Path)" />
      <xsl:variable name="var:v3" select="userCSharp:LogicalEq($var:v2 , '1')" />
      <xsl:if test="string($var:v3)='true'">
        <xsl:variable name="var:v4" select="TimedValues/TimedValue/text()" />
        <ns0:Quantity>
          <xsl:value-of select="$var:v4" />
        </ns0:Quantity>
      </xsl:if>
    </xsl:if>
  </xsl:for-each>
 
  <xsl:for-each select="TimeSeries">
    <xsl:variable name="var:v5" select="string(@Path)" />
    <xsl:variable name="var:v6" select="userCSharp:LogicalEq($var:v5 , '2')" />
    <xsl:if test="$var:v6">
      <xsl:if test="string($var:v6)='true'">
        <xsl:variable name="var:v7" select="TimedValues/TimedValue/text()" />
        <ns0:NRJQuantity>
          <xsl:value-of select="$var:v7" />
        </ns0:NRJQuantity>
      </xsl:if>
    </xsl:if>
  </xsl:for-each>
...

This means that if we have 50 occurrences of the TimeSeries node, we will have 50 iterations for each element that we want to map to the destination schema… so this approach may be easy to implement and somewhat acceptable in small messages is extremely disadvantageous for a large message.

Limitations of this approach:

• Lack of performance

Second Solution: Using Inline XSLT

In this second approach, what we will do is take the XSLT code generated by the compiler, optimize it by removing all unnecessary cycles, and put this code in a Scripting functoid.

To accomplish this, we need to:

• Drag the Scripting functoid to the map grid
  • In the scripting type, select the Inline XSLT option.
  • In the Inline script, put the following code:

<xsl:for-each select="TimeSeries">
  <xsl:if test="string(@Path) = '1' ">
    <Quantity>
      <xsl:value-of select="TimedValues/TimedValue/text()" />
    </Quantity>
  </xsl:if>
  <xsl:if test="string(@Path) = '2' ">
    <NRJQuantity>
      <xsl:value-of select="TimedValues/TimedValue/text()" />
    </NRJQuantity>
  </xsl:if>
  <xsl:if test="string(@Path) = '3' ">
    <AvgCal>
      <xsl:value-of select="TimedValues/TimedValue/text()" />
    </AvgCal>
  </xsl:if>
  <xsl:if test="string(@Path) = '4' ">
    <AvgDens>
      <xsl:value-of select="TimedValues/TimedValue/text()" />
    </AvgDens>
  </xsl:if>

• Finally, drag a link from the Scripting functoid to one element in the destination schema, for example, NRJQuantity.

Limitations of this approach:

• May have some lack of performance if we work with a large message because of some unnecessary iterations in the cycle; however, it is far more efficient than the first solution.
• Some warnings saying that some required field has no incoming link.
• Because we use scripting functoids, we cannot read the entire map visually. We need to open the functoids and read, mainly, the XSLT code.

Third Solution: Using Inline XSLT along with XPath queries

After analyzing all the advantages and disadvantages, I decided that I could optimize even more the XSLT script in order to have a better performance but to do this I would have to use a different approach than the one that was used by the BizTalk mapper engine, and for me this is the best approach to accomplish this type of mapping problem, because basically solves all limitations of previous solutions: it’s easy to create (only need basic knowledge of XSLT and XPath) and don’t have performance problems.

To accomplish this, we need to:

• Replace the code of the Scripting functoid, existing in the previous solution, with:

<xsl:choose>
  <xsl:when test="count(//TimeSeries[@Path='1']) gt 0">
    <Quantity>
      <xsl:value-of select="//TimeSeries[@Path='1']/TimedValues/TimedValue/text()" />
    </Quantity>
  </xsl:when>
</xsl:choose>
<xsl:choose>
  <xsl:when test="count(//TimeSeries[@Path='2']) gt 0">
    <NRJQuantity>
      <xsl:value-of select="//TimeSeries[@Path='2']/TimedValues/TimedValue/text()" />
    </NRJQuantity>
  </xsl:when>
</xsl:choose>
<xsl:choose>
  <xsl:when test="count(//TimeSeries[@Path='3']) gt 0">
    <AvgCal>
      <xsl:value-of select="//TimeSeries[@Path='3']/TimedValues/TimedValue/text()" />
    </AvgCal>
  </xsl:when>
</xsl:choose>
<xsl:choose>
  <xsl:when test="count(//TimeSeries[@Path='4']) gt 0">
    <AvgDens>
      <xsl:value-of select="//TimeSeries[@Path='4']/TimedValues/TimedValue/text()" />
    </AvgDens>
  </xsl:when>
</xsl:choose>

Limitations of this approach:

• Because we use scripting functoids, we cannot read the entire map visually. We need to open the functoids and read, mainly, the XSLT code.
• Need basic knowledge of XSLT and XPath.
• Some warnings saying that some required field has no incoming link.

Fourth Solution: Using Inline XSLT along with XPath queries (avoiding warnings)

So, to avoid warnings saying that some required field has no incoming link, we must split the XSLT code that we use in the last solution (Third Solution) into different blocks for each element in the destination schema

To accomplish this, we need to:

• Drag four Scripting functoid to the map grid and drag a link from each Scripting functoid to each element in the destination schema
• For each Scripting functoid:
  • In the scripting type, select the Inline XSLT option.
  • In the Inline script, put the code that corresponds to the element in the destination element, for example, in the first:

<xsl:choose>
  <xsl:when test="count(//TimeSeries[@Path='1']) gt 0">
    <Quantity>
      <xsl:value-of select="//TimeSeries[@Path='1']/TimedValues/TimedValue/text()" />
    </Quantity>
  </xsl:when>
</xsl:choose>

Limitations of this approach:

• Because we use scripting functoids, we cannot read the entire map visually. We need to open the functoids and read, mainly, the XSLT code.
• Need basic knowledge of XSLT and XPath.

Download

You can download the source code from GitHub:

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