New Page 1

In keeping with my mapping topics I have another inline XSLT scenario.  In my last XSLT scenario entry I discussed grouping by countries and then cities. 

 

In this entry I want to look at a somewhat related scenario.  In this scenario I receive an instance document that contains account numbers and associated amounts.  We need the account numbers grouped and the amounts summed by account number.

 

The instance document looks like:

 

<ns0:Root xmlns:ns0="http://Blogtopic.SchemaSrc">

  <Source>

    <Row ID="1" AccountNbr="1001" Amount="1000" />

    <Row ID="2" AccountNbr="1001" Amount="1000" />

    <Row ID="3" AccountNbr="2002" Amount="1000" />

    <Row ID="4" AccountNbr="1001" Amount="1000" />

    <Row ID="5" AccountNbr="3003" Amount="1000" />

    <Row ID="6" AccountNbr="2002" Amount="1000" />

  </Source>

</ns0:Root>

 

The source schema looks like:

 

 

 

And the destination schema looks like:

 

 

 

As mentioned in my last XSLT blog entry there isn't a way to group records in the BizTalk Mapper so we will use a script functoid with custom XSLT.

 

The first thing we will need to do is to drag a Script functoid onto the Grid.  Then drag a line to the element that we will create the output for.  Again, with the inline XLST functionality we are required to produce the output for all of the elements underneath the records our functoid is attached to.  So in this example we are responsible for creating all of the content under the Destination record.

 

The map will look like this:

 

 

 

The following XSLT can be cut and paste (without the line numbers) into the Inline Script Buffer section of the script functoid after the Script Type drop down has been set to Inline XSLT

 

To create the output in the format that we want we will start with the following XLST.

 

<xsl:variable name="unique-accountNbr"

select="//Source/Row[not(@AccountNbr=preceding-sibling::Row/@AccountNbr)]/@AccountNbr" />

 

This creates a variable named unique-accountNbr and populates it with the list of unique account numbers.  Now we need to output the nodes and loop through the account numbers.  The following code will accomplish that.

 

1.  <Destination>

2.    <xsl:for-each select="$unique-accountNbr">

3.       <Account>

4.          <xsl:variable name="accountSum" select="sum(//Source/Row[@AccountNbr=current()]/@Amount)" />

5.          <xsl:attribute name="AccountNbr">

6.             <xsl:value-of select="current()" />

7.          </xsl:attribute>

8.          <xsl:attribute name="AccountSum">

9.            <xsl:value-of select="$accountSum" />

10.         </xsl:attribute>

11.     </Account>

12.   </xsl:for-each>

13. </Destination>

 

Lets walk through this code.

 

Lines 1 and 13 create the Destination node output and close the node.

 

Line 2 creates the loop that will loop through all of the unique account numbers.

 

Lines 3 and 11 create the Account node output and close the node

 

Line 4 creates another variable for the sum of the amounts by account number

 

Lines 5 and 7 create the AccountNbr attribute and close it

 

Line 6 takes the current account number in the loop and writes the value to the attribute

 

Lines 8, 9 and 10 repeat what was just done for the AccountNbr attribute but this time for the AccountSum attribute.  Also notice that the value of the accountsum variable is used.

 

This code will loop through as many unique account numbers as are contained in the input document.  Then for each unique account number the code will sum up all of the Amount attributes associated with the unique account numbers.

 

The output looks like this:

 

<ns0:Root xmlns:ns0="http://BlogTopic.SchemaDst">

        <Destination>

                <Account AccountNbr="1001" AccountSum="3000"></Account>

                <Account AccountNbr="2002" AccountSum="2000"></Account>

                <Account AccountNbr="3003" AccountSum="1000"></Account>

        </Destination>

</ns0:Root>