Many-to-one mappings

Posted: February 3, 2009  |  Categories: BizTalk Uncategorized

I writing this how to because every time I do this I have forgotten how I did it the last time.

  1. Drop a transform shape into an orchestration that contains two or more message input types and one message output shape. See Thomas for more detail.
  2. Create a test message for testing the map using the steps described by Erwin. Using BTS2006 I never have to do this step; “Remember which definition is referenced to under each InputMessagePart_n-section. Then delete the contents of each InputMessagePart_n-section”.
  3. Create the map using the BizTalk mapper.
  4. Test the map with the message in 2.

I will cover two scenarios ;

1) The values in the output message are a combination of the two input messages.

many21

2) The values in the output message are an aggregation of the two input messages with one value coming from only one input message.

many21withfilters4

Scenario 1

The test message for the scenario 1, X_Many21.btm was as follows;

<ns0:Root xmlns:ns0=”http://schemas.microsoft.com/BizTalk/2003/aggschema”>
  <InputMessagePart_0>
    <ns1:Root xmlns:ns1=”
http://Many21MappingTest.SchemawithrepeatingNode”>
      <RepeatingRecord>
        <Element1>Element1_0</Element1>
        <Element2>Element2_0</Element2>
        <Elementfromothermessage>0</Elementfromothermessage>
      </RepeatingRecord>
        <RepeatingRecord>
            <Element1>Element1_1</Element1>
            <Element2>Element2_1</Element2>
            <Elementfromothermessage>0</Elementfromothermessage>
        </RepeatingRecord>
    </ns1:Root>
  </InputMessagePart_0>
  <InputMessagePart_1>
 <ns2:Root xmlns:ns2=”http://Many21MappingTest.Schemawithotherinformation”>
        <Record>
      <otherinformation>10</otherinformation>
        </Record>  
    </ns2:Root>
  </InputMessagePart_1>
</ns0:Root>

The map is straightforward and on testing the map the output is;

<ns0:Root xmlns:ns0=”http://Many21MappingTest.SchemawithrepeatingNode”>
<RepeatingRecord>
  <Element1>Element1_0</Element1>
  <Element2>Element2_0</Element2>
  <Elementfromothermessage>10</Elementfromothermessage>
  </RepeatingRecord>
<RepeatingRecord>
  <Element1>Element1_1</Element1>
  <Element2>Element2_1</Element2>
  <Elementfromothermessage>10</Elementfromothermessage>
  </RepeatingRecord>
  </ns0:Root>

Scenario 2

This scenario is more complicated and some tricks are required. The test message for the scenario 1, X_Many21withfilters.btm was as follows;

<ns0:Root xmlns:ns0=”http://schemas.microsoft.com/BizTalk/2003/aggschema”>
  <InputMessagePart_0>
   <ns1:Root xmlns:ns1=”
http://Many21MappingTest.SchemawithrepeatingNode”>
        <RepeatingRecord>
            <Element1>UnAssigned</Element1>
            <Element2>UnAssigned</Element2>
            <Elementfromothermessage>10</Elementfromothermessage>
        </RepeatingRecord>
      <RepeatingRecord>
        <Element1>Element1_0</Element1>
        <Element2>Element2_0</Element2>
        <Elementfromothermessage>10</Elementfromothermessage>
      </RepeatingRecord>
    </ns1:Root>
  </InputMessagePart_0>
  <InputMessagePart_1>
   <ns1:Root xmlns:ns1=”
http://Many21MappingTest.SchemawithrepeatingNode”>
      <RepeatingRecord>
        <Element1>Element1_1</Element1>
        <Element2>Element2_1</Element2>
        <Elementfromothermessage>0</Elementfromothermessage>
      </RepeatingRecord>
    </ns1:Root>
  </InputMessagePart_1>
</ns0:Root>

If I used the mapper to create the mapping the message was not quite as desired. It was missing Elementfromother node in the first record i.e.

<ns0:Root xmlns:ns0=”http://Many21MappingTest.SchemawithrepeatingNode”>
<RepeatingRecord>
  <Element1>Element1_1</Element1>
  <Element2>Element2_1</Element2>
 
</RepeatingRecord>
<RepeatingRecord>
  <Element1>Element1_0</Element1>
  <Element2>Element2_0</Element2>
  <Elementfromothermessage>10</Elementfromothermessage>
  </RepeatingRecord>
  </ns0:Root>

I validated the map to see what the generated XSLT was like. This shows that the mapper is not generating the correct XSLT i.e.

<?xml version=”1.0″ encoding=”UTF-16″?>
<xsl:stylesheet xmlns:xsl=”
http://www.w3.org/1999/XSL/Transform” xmlns:msxsl=”urn:schemas-microsoft-com:xslt” xmlns:var=”http://schemas.microsoft.com/BizTalk/2003/var” exclude-result-prefixes=”msxsl var s0 userCSharp” version=”1.0″ xmlns:s0=”http://schemas.microsoft.com/BizTalk/2003/aggschema” xmlns:ns0=”http://Many21MappingTest.SchemawithrepeatingNode” xmlns:userCSharp=”http://schemas.microsoft.com/BizTalk/2003/userCSharp”>
  <xsl:output omit-xml-declaration=”yes” method=”xml” version=”1.0″ />
  <xsl:template match=”/”>
    <xsl:apply-templates select=”/s0:Root” />
  </xsl:template>
  <xsl:template match=”/s0:Root”>
    <ns0:Root>
      <xsl:for-each select=”InputMessagePart_1/ns0:Root/RepeatingRecord”>
        <RepeatingRecord>
          <Element1>
            <xsl:value-of select=”Element1/text()” />
          </Element1>
          <Element2>
            <xsl:value-of select=”Element2/text()” />
          </Element2>
        </RepeatingRecord>
      </xsl:for-each>
      <xsl:for-each select=”InputMessagePart_0/ns0:Root/RepeatingRecord”>
        <xsl:variable name=”var:v1″ select=”userCSharp:LogicalNe(string(Element1/text()) , &quot;UnAssigned&quot;)” />
        <xsl:if test=”$var:v1″>
          <xsl:variable name=”var:v2″ select=”../RepeatingRecord[1]/Elementfromothermessage/text()” />
          <RepeatingRecord>
            <Element1>
              <xsl:value-of select=”Element1/text()” />
            </Element1>
            <Element2>
              <xsl:value-of select=”Element2/text()” />
            </Element2>
            <Elementfromothermessage>
              <xsl:value-of select=”$var:v2″ />
            </Elementfromothermessage>
          </RepeatingRecord>
        </xsl:if>
      </xsl:for-each>
    </ns0:Root>
  </xsl:template>
  <msxsl:script language=”C#” implements-prefix=”userCSharp”><![CDATA[
public bool LogicalNe(string val1, string val2)
{
    bool ret = false;
    double d1 = 0;
    double d2 = 0;
    if (IsNumeric(val1, ref d1) && IsNumeric(val2, ref d2))
    {
        ret = d1 != d2;
    }
    else
    {
        ret = String.Compare(val1, val2, StringComparison.Ordinal) != 0;
    }
    return ret;
}

public bool IsNumeric(string val)
{
    if (val == null)
    {
        return false;
    }
    double d = 0;
    return Double.TryParse(val, System.Globalization.NumberStyles.AllowThousands | System.Globalization.NumberStyles.Float, System.Globalization.CultureInfo.InvariantCulture, out d);
}

public bool IsNumeric(string val, ref double d)
{
    if (val == null)
    {
        return false;
    }
    return Double.TryParse(val, System.Globalization.NumberStyles.AllowThousands | System.Globalization.NumberStyles.Float, System.Globalization.CultureInfo.InvariantCulture, out d);
}

]]></msxsl:script>
</xsl:stylesheet>

I saved the above as an xsl file and imported this into my project. I edited the xsl as;

<?xml version=”1.0″ encoding=”UTF-16″?>
<xsl:stylesheet xmlns:xsl=”
http://www.w3.org/1999/XSL/Transform” xmlns:msxsl=”urn:schemas-microsoft-com:xslt” xmlns:var=”http://schemas.microsoft.com/BizTalk/2003/var” exclude-result-prefixes=”msxsl var s0 userCSharp” version=”1.0″ xmlns:s0=”http://schemas.microsoft.com/BizTalk/2003/aggschema” xmlns:ns0=”http://Many21MappingTest.SchemawithrepeatingNode” xmlns:userCSharp=”http://schemas.microsoft.com/BizTalk/2003/userCSharp”>
  <xsl:output omit-xml-declaration=”yes” method=”xml” version=”1.0″ />
  <xsl:template match=”/”>
    <xsl:apply-templates select=”/s0:Root” />
  </xsl:template>
  <xsl:template match=”/s0:Root”>
    <ns0:Root>
   &nbs
p;  <xsl:for-each select=”InputMessagePart_0/ns0:Root/RepeatingRecord”>
        <xsl:variable name=”var:v1″ select=”userCSharp:LogicalNe(string(Element1/text()) , &quot;UnAssigned&quot;)” />
              <xsl:if test=”$var:v1″>
                  <xsl:variable name=”var:v2″ select=”../RepeatingRecord[1]/Elementfromothermessage/text()” />
         <RepeatingRecord>
            <Element1>
              <xsl:value-of select=”Element1/text()” />
            </Element1>
            <Element2>
              <xsl:value-of select=”Element2/text()” />
            </Element2>
            <Elementfromothermessage>
              <xsl:value-of select=”$var:v2″ />
            </Elementfromothermessage>
          </RepeatingRecord>

……….etc.

I then changed the map to use a custom xsl path. Now if you run the test again I get

<ns0:Root xmlns:ns0=”http://Many21MappingTest.SchemawithrepeatingNode”>
<RepeatingRecord>
  <Element1>Element1_0</Element1>
  <Element2>Element2_0</Element2>
  <Elementfromothermessage>10</Elementfromothermessage>
  </RepeatingRecord>
<RepeatingRecord>
  <Element1>Element1_1</Element1>
  <Element2>Element2_1</Element2>
  <Elementfromothermessage>10</Elementfromothermessage>
  </RepeatingRecord>
  </ns0:Root>

For a better solution see http://connectedpawns.wordpress.com/2009/03/08/many-to-one-mappings-using-the-table-looping-functoid/

turbo360

Back to Top