Generating a Cross Product from two input schemas using BizTalk Map

Question

I am using a many to one mapping in BizTalk, to generate an output schema with data generated using a cross product logic on a node of input schemas.

Following figure depicts what I've done yet:

The sample input xmls are as follows:

<!-Schema1 Instance-->
<Root>
    <Data>
        <ItemCode>10</ItemCode>
        <ItemCost>1024</ItemCost>
    </Data>
    <Data>
        <ItemCode>20</ItemCode>
        <ItemCost>2048</ItemCost>
    </Data>
</Root>

<!-Schema2 Instance-->
<Root>
    <Data>
        <Code>10</Code>
        <ShipAddr>addr11101</ShipAddr>
    </Data>
    <Data>
        <Code>30</Code>
        <ShipAddr>addr33301</ShipAddr>
    </Data>
    <Data>
        <Code>20</Code>
        <ShipAddr>addr22201</ShipAddr>
    </Data>
    <Data>
        <Code>10</Code>
        <ShipAddr>addr11102</ShipAddr>
    </Data>
</Root>

The required output is based on a cross product performed based on equality of Schema1.ItemCode and Schema2.Code. Sample is as follows:

<!--Output Schema Instance required; Order of records is irrelevant-->
<Root>
    <Data>
        <Code>10</Code>
        <ItemCost>1024</ItemCost>
        <ShipAddr>addr11101</ShipAddr>
    </Data>
    <Data>
        <Code>20</Code>
        <ItemCost>2048</ItemCost>
        <ShipAddr>addr22201</ShipAddr>
    </Data>
    <Data>
        <Code>10</Code>
        <ItemCost>1024</ItemCost>
        <ShipAddr>addr11102</ShipAddr>
    </Data>
</Root>

Actual output:

1. Output with no looping functoid

XML Output

<ns0:Root xmlns:ns0="http://TestTO_DELETE.SchemaOut">
    <Data>
        <Code>10</Code><ItemCost>1024</ItemCost><ShipAddr>addr11101</ShipAddr>
    </Data>
    <Data><Code>20</Code></Data>
</ns0:Root>

2. Output with both looping functoids connections 1, and 2

XML Output

<ns0:Root xmlns:ns0="http://TestTO_DELETE.SchemaOut">
    <Data>
        <Code>10</Code>
    </Data>
    <Data>
        <Code>20</Code>
    </Data>
    <Data />
    <Data />
    <Data />
    <Data />
</ns0:Root>

3. Output with single looping functoid connection 1

XML Output

<ns0:Root xmlns:ns0="http://TestTO_DELETE.SchemaOut">
    <Data>
        <Code>10</Code><ItemCost>1024</ItemCost><ShipAddr>addr11101</ShipAddr>
    </Data>
    <Data>
        <Code>20</Code>
    </Data>
</ns0:Root>

Please suggest how to proceed in such scenario?

Answer 1

I tried various combinations of functoids to get the required output schema, but nothing worked. So, I finally moved on to use scripting functoid, which served my purpose. I am posting my finding as it could be helpful to someone else.

This is how I proceeded:

1. Remove all connections and functoids from the map
2. Add a scripting functoid
3. Connect InputMesagePart_0 to input of scripting functoid
4. Connect Scripting functoid to the first element node of the output schema
5. In the Script Functoid Configuration, add the transformation logic. For e.g., in my case the logic was:

<xsl:template name="Template1">
    <xsl:param name="MessagePart_0_Xml" /> <!--Not used anywhere-->
    <xsl:variable name="Msg_0_RootNode" select="/*[local-name()='Root']/*[local-name()='InputMessagePart_0']/*[local-name()='Root']" />
    <xsl:variable name="Msg_1_RootNode" select="/*[local-name()='Root']/*[local-name()='InputMessagePart_1']/*[local-name()='Root']" />
    <xsl:for-each select="$Msg_0_RootNode/Data">
        <xsl:variable name="Msg_0_DataNode" select="." />
        <xsl:for-each select="$Msg_1_RootNode/Data">
	        <xsl:variable name="Msg_1_DataNode" select="." />
	        <xsl:if test="$Msg_0_DataNode/ItemCode/text() = $CostCenterDataNode/Code/text()">
	            <ItemCost>
	                <xsl:value-of select="$Msg_0_DataNode/ItemCost/text()" />
                </ItemCost>
	            <ShipAddr>
                    <xsl:value-of select="$CostCenterDataNode/ShipAddr/text()" />
	            </ShipAddr>
            </xsl:if>
        </xsl:for-each>
    </xsl:for-each>
</xsl:template>

If there is a better way to approach this problem, please suggest.