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>