BizTalk XSLT mapping

Question

I have two input csv files one for the Member and One for the Benefit they look like below

Member xml looks like

  <Root xmlns="http://TestTwoInput.MemberSchema">
  <Record xmlns="">
  <EmployeeID>12</EmployeeID> 
  <MemberText>MEMBER</MemberText> 
  <SPID>007609952</SPID> 
  <MPID>007609952</MPID> 
  <SAID>12</SAID> 
  <ACode>05</ACode> 
  </Record>
  <Record xmlns="">
  <EmployeeID>14</EmployeeID> 
  <MemberText>MEMBER</MemberText> 
  <SPID>004482352</SPID> 
  <MPID>004482352</MPID> 
  <SAID>14</SAID> 
  <ACode>05</ACode> 
  </Record>
  </Root>

Benefit xml looks like

 <Root xmlns="http://TestTwoInput.BenefitSchema">
 <Record xmlns="">
  <EmployeeID>12</EmployeeID> 
  <BenefitText>BENEFIT</BenefitText> 
  <BCode>MEA</BCode> 
  <ELR>001</ELR> 
  <Control>0100189</Control> 
  </Record>
 <Record xmlns="">
  <EmployeeID>12</EmployeeID> 
  <BenefitText>BENEFIT</BenefitText> 
  <BCode>DEN</BCode> 
  <ELR>002</ELR> 
  <Control>0100189</Control> 
  </Record>
 <Record xmlns="">
  <EmployeeID>14</EmployeeID> 
  <BenefitText>BENEFIT</BenefitText> 
  <BCode>DEN</BCode> 
  <ELR>002</ELR> 
  <Control>0100189</Control> 
  </Record>
 <Record xmlns="">
  <EmployeeID>14</EmployeeID> 
  <BenefitText>BENEFIT</BenefitText> 
  <BCode>MEA</BCode> 
  <ELR>001</ELR> 
  <Control>0100189</Control> 
  </Record>
 <Record xmlns="">
  <EmployeeID>14</EmployeeID> 
  <BenefitText>BENEFIT</BenefitText> 
  <BCode>MEA</BCode> 
  <ELR>001</ELR> 
  <Control>0100189</Control> 
  </Record>
 <Record xmlns="">
  <EmployeeID>14</EmployeeID> 
  <BenefitText>BENEFIT</BenefitText> 
  <BCode>DEN</BCode> 
  <ELR>002</ELR> 
  <Control>0100189</Control> 
  </Record>
  </Root>

Both the inputs have EmployeeID field. I need to map both the files in to one output like each member with their Benefit

12,MEMBER,007609952,007609952,12,05
12,BENEFIT,MEA,001,0100189
12,BENEFIT,DEN,002,0100189
14,MEMBER,004482352,004482352,14,05
14,BENEFIT,DEN,002,0100189
14,BENEFIT,MEA,001,0100189
14,BENEFIT,MEA,001,0100189
14,BENEFIT,DEN,002,0100189

This is my map But this throws the output like

12,MEMBER,007609952,007609952,12,05
12,BENEFIT,MEA,001,0100189
12,BENEFIT,DEN,002,0100189
,,,,
,,,,
,,,,
,,,,
14,MEMBER,004482352,004482352,14,05
12,BENEFIT,MEA,001,0100189
12,BENEFIT,DEN,002,0100189
,,,,
,,,,
,,,,
,,,,

Below XSLT for mapping

<?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 s1 s0 s2 userCSharp" version="1.0" xmlns:s1="http://TestTwoInput.BenefitSchema" xmlns:ns0="http://TestTwoInput.OutputSchema" xmlns:s0="http://TestTwoInput.MemberSchema" xmlns:s2="http://schemas.microsoft.com/BizTalk/2003/aggschema" 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="/s2:Root" />
</xsl:template>
<xsl:template match="/s2:Root">
<ns0:Root>
  <xsl:for-each select="InputMessagePart_0/s0:Root/Record">
    <Record>
      <Member>
        <EID>
          <xsl:value-of select="EmployeeID/text()" />
        </EID>
        <Text>
          <xsl:value-of select="MemberText/text()" />
        </Text>
        <SPID>
          <xsl:value-of select="SPID/text()" />
        </SPID>
        <MPID>
          <xsl:value-of select="MPID/text()" />
        </MPID>
        <SIAD>
          <xsl:value-of select="SAID/text()" />
        </SIAD>
        <ACode>
          <xsl:value-of select="ACode/text()" />
        </ACode>
      </Member>
      <xsl:for-each select="../../../InputMessagePart_1/s1:Root/Record">
        <xsl:variable name="var:v1" select="userCSharp:LogicalEq(string(EmployeeID/text()) , string(../../../InputMessagePart_0/s0:Root/Record/EmployeeID/text()))" />
        <xsl:variable name="var:v3" select="string(EmployeeID/text())" />
        <xsl:variable name="var:v4" select="string(../../../InputMessagePart_0/s0:Root/Record/EmployeeID/text())" />
        <xsl:variable name="var:v5" select="userCSharp:LogicalEq($var:v3 , $var:v4)" />
        <Benefit>
          <xsl:if test="string($var:v1)='true'">
            <xsl:variable name="var:v2" select="EmployeeID/text()" />
            <ID>
              <xsl:value-of select="$var:v2" />
            </ID>
          </xsl:if>
          <xsl:if test="string($var:v5)='true'">
            <xsl:variable name="var:v6" select="BenefitText/text()" />
            <Text>
              <xsl:value-of select="$var:v6" />
            </Text>
          </xsl:if>
          <xsl:if test="string($var:v5)='true'">
            <xsl:variable name="var:v7" select="BCode/text()" />
            <BCode>
              <xsl:value-of select="$var:v7" />
            </BCode>
          </xsl:if>
          <xsl:if test="string($var:v5)='true'">
            <xsl:variable name="var:v8" select="ELR/text()" />
            <ELR>
              <xsl:value-of select="$var:v8" />
            </ELR>
          </xsl:if>
          <xsl:if test="string($var:v5)='true'">
            <xsl:variable name="var:v9" select="Control/text()" />
            <Control>
              <xsl:value-of select="$var:v9" />
            </Control>
          </xsl:if>
        </Benefit>
      </xsl:for-each>
    </Record>
   </xsl:for-each>
  </ns0:Root>
</xsl:template>
<msxsl:script language="C#" implements-prefix="userCSharp"><![CDATA[
public bool LogicalEq(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 am not sure how to achieve the output through the XSLT. Any help with this is greatly appreciated

Answer 1

Ok. So your input message as posted isn't quite what it'd look like in the aggschema format, which would be like this:

<Root xmlns="http://schemas.microsoft.com/BizTalk/2003/aggschema">
  <InputMessagePart_0 xmlns="">
    <Root xmlns="http://TestTwoInput.MemberSchema">
      <Record xmlns="">
        <EmployeeID>12</EmployeeID>
        <MemberText>MEMBER</MemberText>
        <SPID>007609952</SPID>
        <MPID>007609952</MPID>
        <SAID>12</SAID>
        <ACode>05</ACode>
      </Record>
      <Record xmlns="">
        <EmployeeID>14</EmployeeID>
        <MemberText>MEMBER</MemberText>
        <SPID>004482352</SPID>
        <MPID>004482352</MPID>
        <SAID>14</SAID>
        <ACode>05</ACode>
      </Record>
    </Root>
  </InputMessagePart_0>
  <InputMessagePart_1 xmlns="">
    <Root xmlns="http://TestTwoInput.BenefitSchema">
      <Record xmlns="">
        <EmployeeID>12</EmployeeID>
        <BenefitText>BENEFIT</BenefitText>
        <BCode>MEA</BCode>
        <ELR>001</ELR>
        <Control>0100189</Control>
      </Record>
      <Record xmlns="">
        <EmployeeID>12</EmployeeID>
        <BenefitText>BENEFIT</BenefitText>
        <BCode>DEN</BCode>
        <ELR>002</ELR>
        <Control>0100189</Control>
      </Record>
      <Record xmlns="">
        <EmployeeID>14</EmployeeID>
        <BenefitText>BENEFIT</BenefitText>
        <BCode>DEN</BCode>
        <ELR>002</ELR>
        <Control>0100189</Control>
      </Record>
      <Record xmlns="">
        <EmployeeID>14</EmployeeID>
        <BenefitText>BENEFIT</BenefitText>
        <BCode>MEA</BCode>
        <ELR>001</ELR>
        <Control>0100189</Control>
      </Record>
      <Record xmlns="">
        <EmployeeID>14</EmployeeID>
        <BenefitText>BENEFIT</BenefitText>
        <BCode>MEA</BCode>
        <ELR>001</ELR>
        <Control>0100189</Control>
      </Record>
      <Record xmlns="">
        <EmployeeID>14</EmployeeID>
        <BenefitText>BENEFIT</BenefitText>
        <BCode>DEN</BCode>
        <ELR>002</ELR>
        <Control>0100189</Control>
      </Record>
    </Root>
  </InputMessagePart_1>
</Root>

This scenario doesn't really strictly require Muenchian grouping, it just requires a bit more control over the for-each (looping functiods in BizTalk) which BizTalk doesn't allow through the standard mapping designer. If you look at the output XSLT you'll notice that it's doing some non-sensical stuff in terms of how the for-each loops are nested and how it's trying to test for equality between the EmployeeID nodes. Here's how the custom XSLT should look, properly nested (and removing BizTalk's generated and unnecessary variables):

<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 s1 s0 s2 userCSharp" version="1.0" xmlns:s1="http://TestTwoInput.BenefitSchema" xmlns:ns0="http://TestTwoInput.OutputSchema" xmlns:s0="http://TestTwoInput.MemberSchema" xmlns:s2="http://schemas.microsoft.com/BizTalk/2003/aggschema" xmlns:userCSharp="http://schemas.microsoft.com/BizTalk/2003/userCSharp">
  <xsl:output omit-xml-declaration="yes" indent="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/s2:Root" />
  </xsl:template>
  <xsl:template match="/s2:Root">
    <ns0:Root>
      <xsl:for-each select="InputMessagePart_0/s0:Root/Record">        
        <Record>
          <Member>
            <EID>
              <xsl:value-of select="EmployeeID/text()" />
            </EID>
            <Text>
              <xsl:value-of select="MemberText/text()" />
            </Text>
            <SPID>
              <xsl:value-of select="SPID/text()" />
            </SPID>
            <MPID>
              <xsl:value-of select="MPID/text()" />
            </MPID>
            <SIAD>
              <xsl:value-of select="SAID/text()" />
            </SIAD>
            <ACode>
              <xsl:value-of select="ACode/text()" />
            </ACode>
          </Member>
          <xsl:variable name="empID" select="EmployeeID" />
          <xsl:for-each select="../../../InputMessagePart_1/s1:Root/Record[EmployeeID = $empID]">
            <Benefit>              
              <ID>
                <xsl:value-of select="EmployeeID/text()" />
              </ID>
              <Text>
                <xsl:value-of select="BenefitText/text()" />
              </Text>
              <BCode>
                <xsl:value-of select="BCode/text()" />
              </BCode>
              <ELR>
                <xsl:value-of select="ELR/text()" />
              </ELR>
              <Control>
                <xsl:value-of select="Control/text()" />
              </Control>
            </Benefit>
          </xsl:for-each>
        </Record>
      </xsl:for-each>
    </ns0:Root>
  </xsl:template>
</xsl:stylesheet>

This gives you the following output:

<ns0:Root xmlns:ns0="http://TestTwoInput.OutputSchema">
   <Record>
      <Member>
         <EID>12</EID>
         <Text>MEMBER</Text>
         <SPID>007609952</SPID>
         <MPID>007609952</MPID>
         <SIAD>12</SIAD>
         <ACode>05</ACode>
      </Member>
      <Benefit>
         <ID>12</ID>
         <Text>BENEFIT</Text>
         <BCode>MEA</BCode>
         <ELR>001</ELR>
         <Control>0100189</Control>
      </Benefit>
      <Benefit>
         <ID>12</ID>
         <Text>BENEFIT</Text>
         <BCode>DEN</BCode>
         <ELR>002</ELR>
         <Control>0100189</Control>
      </Benefit>
   </Record>
   <Record>
      <Member>
         <EID>14</EID>
         <Text>MEMBER</Text>
         <SPID>004482352</SPID>
         <MPID>004482352</MPID>
         <SIAD>14</SIAD>
         <ACode>05</ACode>
      </Member>
      <Benefit>
         <ID>14</ID>
         <Text>BENEFIT</Text>
         <BCode>DEN</BCode>
         <ELR>002</ELR>
         <Control>0100189</Control>
      </Benefit>
      <Benefit>
         <ID>14</ID>
         <Text>BENEFIT</Text>
         <BCode>MEA</BCode>
         <ELR>001</ELR>
         <Control>0100189</Control>
      </Benefit>
      <Benefit>
         <ID>14</ID>
         <Text>BENEFIT</Text>
         <BCode>MEA</BCode>
         <ELR>001</ELR>
         <Control>0100189</Control>
      </Benefit>
      <Benefit>
         <ID>14</ID>
         <Text>BENEFIT</Text>
         <BCode>DEN</BCode>
         <ELR>002</ELR>
         <Control>0100189</Control>
      </Benefit>
   </Record>
</ns0:Root>

Here's an XSLTransform of the above to play with: http://xsltransform.net/3NSSEvs

You might get better performance by using some xsl:keys (Muenchian grouping) - do some research on that if you're going to be using this on larger document resultsets; but if your documents look like what you've sent here typically, you should be fine. To be honest, if they get substantially large, I would advise refactoring things at the source rather than trying to solve it all using XSLT - limiting whatever process is producing the flat files if possible (perhaps doing more work in SQL if this is coming from SQL to actually merge some of the data and/or page through it), or writing a custom component in C# to handle merging them more efficiently than the XSLT engine will end up being able to do.