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

Introduction

This article has its origin in a question posted in BizTalk Server forums: Reg BizTalk Mapping and will provide you with common possible solutions and try to explain what’s the best way to address and solve this problem.

Note: you can find all mapping logic of this exercise in the forum, however briefly we have a repeating node “TimeSeries” and based on Path attribute value of “TimeSeries” node we will map on 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”

Question/Problem

Kindly help me which mapping to be used by keeping performance in mind

Following is the input:

<GetPIArchiveDataResult>
     <TimeSeries xsi:type="TimeSeriesUpdates" Path="1" ErrDesc=""  Error="-2147220478" SeriesID="">
          <TimedValues>
               <TimedValue Path="1" Flags="Flags_1" Time="1999-05-31T13:20:00.000-05:00" UOM="UOM_3" Status="Status_4" PctGood="10" DataType="DataType_6">2458.25</TimedValue>
          </TimedValues>
          <Updates></Updates>
     </TimeSeries>
     <TimeSeries xsi:type="TimeSeriesUpdates" Path="2" ErrDesc=""  Error="-2147220478" SeriesID="">
          <TimedValues>
               <TimedValue Path="2" Flags="Flags_1" Time="1999-05-31T13:20:00.000-05:00" UOM="UOM_3" Status="Status_4" PctGood="10" DataType="DataType_6">3458.25</TimedValue>
          </TimedValues>
          <Updates></Updates>
     </TimeSeries>
     <TimeSeries xsi:type="TimeSeriesUpdates" Path="3" ErrDesc=""  Error="-2147220478" SeriesID="">
          <TimedValues><TimedValue Path="3" Flags="Flags_1" Time="1999-05-31T13:20:00.000-05:00" UOM="UOM_3" Status="Status_4" PctGood="10" DataType="DataType_6">4458.25</TimedValue>
          </TimedValues>
          <Updates></Updates>
     </TimeSeries>
     <TimeSeries xsi:type="TimeSeriesUpdates" Path="4" ErrDesc=""  Error="-2147220478" SeriesID="">
          <TimedValues><TimedValue Path="4" Flags="Flags_1" Time="1999-05-31T13:20:00.000-05:00" UOM="UOM_3" Status="Status_4" PctGood="10" DataType="DataType_6">5458.25</TimedValue>
          </TimedValues>
          <Updates></Updates>
     </TimeSeries>
</GetPIArchiveDataResult>

And My Output should be:

<ns0:Req xmlns:ns0="http://SQLPollingLearnings.SCMReq">
     <Quantity>2458.25</Quantity>
     <NRJQuantity>3458.25</NRJQuantity>
     <AvgCal>4458.25</AvgCal>
     <AvgDens>5458.25</AvgDens>
</ns0:Req>

The Logic for Output is:
Based on Path attribute value of TimeSeries Node(Xpath: /*[local-name()='GetPIArchiveDataResult' and namespace-uri()='']/*[local-name()='TimeSeries' and namespace-uri()='']/@*[local-name()='Path' and namespace-uri()=''])

if(Path=="1")

then assign the value of TimedValue Node to Quantity Node

if(Path=="2")

then assign the TimedValue to NRJQuantity

if(Path=="3")

then assign the TimedValue to AvgCal

if(Path=="4")

then assign the TimedValue to AvgDens

Kindly help me

Possible Solutions

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 “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 “Quantity” element 
• We need to repeat the above steps for all the element until we get the following map:

This solution is correct, it was also suggested by Mohit Kumar Gupta and marked an corrected answer in the forum. In fact this is what’s normally we found in this type of mapping problems 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 in 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 windows
• Right-click and select “View Source” option

We will see that for each element in the destination schema it will be 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 “TimeSeries” node, we will have 50 iterations for each element that we want to map in the destination schema… so this approach may be easy to implement and somewhat acceptable in small messages is extremely disadvantageous for large message.

Limitations of this approach

• Lack of performance

Original source by Sandro Pereira: BizTalk Mapper Patterns: How to map values from a repeating node into a single node using conditions

Second Solution: Using Inline XSLT

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

To accomplish this, we need to:

• Drag Scripting functoid to the map grid
  • In the scripting type select “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>
</xsl:for-each>

• 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 large message because 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.

Original source by Sandro Pereira: BizTalk Mapper Patterns: How to map values from a repeating node into a single node using conditions

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, by:

<xsl:choose>
  <xsl:when test="count(//TimeSeries[@Path='1']) > 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']) > 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']) > 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']) > 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.

Original source by Sandro Pereira: BizTalk Mapper Patterns: How to map values from a repeating node into a single node using conditions

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) in 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 “Inline XSLT” option
  • In the Inline script put the code that corresponding to the element in the destination element, for example in the first:

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

Original source by Sandro Pereira: BizTalk Mapper Patterns: How to map values from a repeating node into a single node using conditions

Conclusion

You need to remember and learn that there’s never only a single way and there are no right or wrong way method to solve a mapping problem, we can find more effective ways (performances) than others or easiest ways to solve the problem, yet the output generated will the same. Quite often, deciding which way is the best approach to use or have the right balance between the easier and effective way can be difficult and often the approach used depend on the expertise and personal choice of the developers or company guidelines.

These are my personal basic guidelines:

• First guideline: I prefer to use the built-in functoids whenever possible unless is not possible to accomplished or the functoid chain becomes too complex to unravel easily.
• Second guideline: I turn to custom scripting functoids, XSLT or C#, only when I cannot solve my problem with the built-in functoids or is too complex to accomplish using built-in functoids. A good example for using scripting functoids is to solve grouping problems, complex transformations rules with multiple elements or if/else decisions inside cycles.
• Third guideline: If is a repeated transformation rule that you can use in several maps you then should create use/create a custom functoid to solve this problem.

However, this rules are derived from several factors that help me decide whether to use one option or the other:

• Reusability: can this transformation be reused several times in this map or can be reused in several maps?
• Level of effort: will this transformation cost me several hours of work?
• Complexity: will this transformation be easier to read and maintain?
• Performance: is this approach which that will bring me more profits?

Code Samples

All of this sample can be found and downloaded in Microsoft Code Gallery:

• How to map values from a repeating node into a single node using conditions

See Also

Read suggested related topics:

• BizTalk Server: BizTalk Mapper Patterns

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.