XSLT Grouping with addition/combination of sub elements

Question

I have the following XML

    <InvestmentAccount Id="Element01_Source3_Sequqence002" Type="Standard" InvestmentStrategyId="Employer" ParameterOverrideIds="AllocationRateOverride">
      <Investment FundName="Fund032" FundValue="4754.82" />
      <Investment FundName="Fund034" FundValue="4643.48" />
      <Investment FundName="Fund035" FundValue="2509.46" />
      <Investment FundName="Fund038" FundValue="7104.71" />
      <Investment FundName="Fund042" FundValue="4244.08" />
    </InvestmentAccount>
    <InvestmentAccount Id="Element01_Source4_Sequence003" Type="DWPRebate" InvestmentStrategyId="DSS" ParameterOverrideIds="DWPAllocationRateOverride">
      <Investment FundName="Fund032" FundValue="1881.76" />
      <Investment FundName="Fund034" FundValue="1584.18" />
      <Investment FundName="Fund035" FundValue="872.99" />
      <Investment FundName="Fund038" FundValue="2899.53" />
      <Investment FundName="Fund042" FundValue="1762.62" />
    </InvestmentAccount>
     <InvestmentAccount Id="Element01_Source2_Sequence001" Type="Standard" InvestmentStrategyId="Employee" ParameterOverrideIds="AllocationRateOverride">
      <Investment FundName="Fund032" FundValue="7395.91" />
      <Investment FundName="Fund034" FundValue="7222.72" />
      <Investment FundName="Fund035" FundValue="3903.52" />
      <Investment FundName="Fund038" FundValue="11051.32" />
      <Investment FundName="Fund042" FundValue="6602.54" />
    </InvestmentAccount>
    <InvestmentAccount Id="Element02_Source2_Sequence004" Type="TransferNonPR" InvestmentStrategyId="Employee" ParameterOverrideIds="AllocationRateOverride">
      <Investment FundName="Fund032" FundValue="1439.29" />
      <Investment FundName="Fund034" FundValue="1614.31" />
      <Investment FundName="Fund035" FundValue="863.68" />
      <Investment FundName="Fund038" FundValue="2153.80" />
      <Investment FundName="Fund042" FundValue="1306.45" />
    </InvestmentAccount>
    <InvestmentAccount Id="Element03_Source2_Sequence005" Type="TransferNonPR" InvestmentStrategyId="Employee" ParameterOverrideIds="AllocationRateOverride">
      <Investment FundName="Fund032" FundValue="9617.42" />
      <Investment FundName="Fund034" FundValue="10787.03" />
      <Investment FundName="Fund035" FundValue="5771.18" />
      <Investment FundName="Fund038" FundValue="14391.20" />
      <Investment FundName="Fund042" FundValue="8729.81" />
      <Investment FundName="fictiousextra" FundValue="1414" />
    </InvestmentAccount>

What I'd like to do is where InvestmentStrategyId AND Type are the same as is the case with the the last 2 above (reorder for clarity) is where the FundName is the same I need to sum the fund values. In this case there are the same in each side but there might be some extra or less on each side.

So the result is I need access to FundName and either FundValue or I can sum or an already summed value.

Help!

Right so this is the output I am trying to achieve.

        <InvestmentAccount Id="Element01_Source3_Sequence002" Type="Standard" InvestmentStrategyId="Employer" ParameterOverrideIds="AllocationRateOverride">
          <Investment FundName="Fund032" FundValue="4754.82" />
          <Investment FundName="Fund034" FundValue="4643.48" />
          <Investment FundName="Fund035" FundValue="2509.46" />
          <Investment FundName="Fund038" FundValue="7104.71" />
          <Investment FundName="Fund042" FundValue="4244.08" />
        </InvestmentAccount>
        <InvestmentAccount Id="Element01_Source4_Sequence003" Type="DWPRebate" InvestmentStrategyId="DSS" ParameterOverrideIds="DWPAllocationRateOverride">
          <Investment FundName="Fund032" FundValue="1881.76" />
          <Investment FundName="Fund034" FundValue="1584.18" />
          <Investment FundName="Fund035" FundValue="872.99" />
          <Investment FundName="Fund038" FundValue="2899.53" />
          <Investment FundName="Fund042" FundValue="1762.62" />
        </InvestmentAccount>
         <InvestmentAccount Id="Element01_Source2_Sequence001" Type="Standard" InvestmentStrategyId="Employee" ParameterOverrideIds="AllocationRateOverride">
          <Investment FundName="Fund032" FundValue="7395.91" />
          <Investment FundName="Fund034" FundValue="7222.72" />
          <Investment FundName="Fund035" FundValue="3903.52" />
          <Investment FundName="Fund038" FundValue="11051.32" />
          <Investment FundName="Fund042" FundValue="6602.54" />
        </InvestmentAccount>
<!-- THIS ONE IS THE SUMMED COMBINTION DUE TO InvestmentStrategyId and Type being multiply occuring -->
<InvestmentAccount ...>
          <Investment FundName="Fund032" FundValue="11056.71" />
          <Investment FundName="Fund034" FundValue="12401.34" />
          <Investment FundName="Fund035" FundValue="6634.86" />
          <Investment FundName="Fund038" FundValue="16545" />
          <Investment FundName="Fund042" FundValue="10036.26" />
          <Investment FundName="fictiousextra" FundValue="1414" />
</InvestmentAccount>

Including any FundNames that are present in 1 and not the other.

I should add I am running using .net 4.0

Answer 1

The XSLT 1.0 would make use of Muenchian Grouping.

I think in this case, you are grouping twice. First you are grouping by InvestmentAccount elements, so you would need a key like so

<xsl:key name="Accounts" match="InvestmentAccount" 
         use="concat(@Type, '|', @InvestmentStrategyId)" />

And then, you also need to group by Investment elements within the account.

<xsl:key name="Investments" match="Investment" 
         use="concat(../@Type, '|', ../@InvestmentStrategyId, '|', @FundName)" />

Do note the use of the pipe in the concatenetion. This could be any character, but it must be one that does not feature in any of the attributes.

To group by InvestmentAccount elements, you can then just match the first element in each group like the following:

<xsl:apply-templates 
  select="InvestmentAccount[
    generate-id() = 
    generate-id(key('Accounts', concat(@Type, '|', @InvestmentStrategyId))[1])]" />

And once in the group, you can get all the Investment elements like so:

 <xsl:apply-templates 
   select="//InvestmentAccount
     [@Type=current()/@Type]
     [@InvestmentStrategyId = current()/@InvestmentStrategyId]/Investment
        [generate-id() = 
         generate-id(key('Investments', 
           concat(../@Type, '|', ../@InvestmentStrategyId, '|', @FundName))[1])]" />

Here is the full XSLT (Do note I have assumed a root element called Investments

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="xml" indent="yes"/>

   <xsl:key name="Accounts" match="InvestmentAccount" use="concat(@Type, '|', @InvestmentStrategyId)" />
   <xsl:key name="Investments" match="Investment" use="concat(../@Type, '|', ../@InvestmentStrategyId, '|', @FundName)" />

   <xsl:template match="/Investments">
      <xsl:apply-templates select="InvestmentAccount[generate-id() = generate-id(key('Accounts', concat(@Type, '|', @InvestmentStrategyId))[1])]" />
   </xsl:template>

   <xsl:template match="InvestmentAccount">
      <xsl:copy>
         <xsl:copy-of select="@*" />
         <xsl:apply-templates select="//InvestmentAccount[@Type=current()/@Type][@InvestmentStrategyId = current()/@InvestmentStrategyId]/Investment[generate-id() = generate-id(key('Investments', concat(../@Type, '|', ../@InvestmentStrategyId, '|', @FundName))[1])]" />
      </xsl:copy>   
   </xsl:template>

   <xsl:template match="Investment">
      <xsl:copy>
         <xsl:copy-of select="@FundName" />
         <xsl:attribute name="FundValue"><xsl:value-of select="format-number(sum(key('Investments', concat(../@Type, '|', ../@InvestmentStrategyId, '|', @FundName))/@FundValue), '0.00')" /></xsl:attribute>
      </xsl:copy>   
   </xsl:template>

   <xsl:template match="@*|node()">
      <xsl:copy>
         <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
   </xsl:template>
</xsl:stylesheet>

When applied to your sample XML (with a root element of Investments), the following is output:

<InvestmentAccount Id="Element01_Source3_Sequqence002" Type="Standard" InvestmentStrategyId="Employer" ParameterOverrideIds="AllocationRateOverride">
   <Investment FundName="Fund032" FundValue="4754.82" />
   <Investment FundName="Fund034" FundValue="4643.48" />
   <Investment FundName="Fund035" FundValue="2509.46" />
   <Investment FundName="Fund038" FundValue="7104.71" />
   <Investment FundName="Fund042" FundValue="4244.08" />
</InvestmentAccount>
   <InvestmentAccount Id="Element01_Source4_Sequence003" Type="DWPRebate" InvestmentStrategyId="DSS" ParameterOverrideIds="DWPAllocationRateOverride">
   <Investment FundName="Fund032" FundValue="1881.76" />
   <Investment FundName="Fund034" FundValue="1584.18" />
   <Investment FundName="Fund035" FundValue="872.99" />
   <Investment FundName="Fund038" FundValue="2899.53" />
   <Investment FundName="Fund042" FundValue="1762.62" />
</InvestmentAccount>
   <InvestmentAccount Id="Element01_Source2_Sequence001" Type="Standard" InvestmentStrategyId="Employee" ParameterOverrideIds="AllocationRateOverride">
   <Investment FundName="Fund032" FundValue="7395.91" />
   <Investment FundName="Fund034" FundValue="7222.72" />
   <Investment FundName="Fund035" FundValue="3903.52" />
   <Investment FundName="Fund038" FundValue="11051.32" />
   <Investment FundName="Fund042" FundValue="6602.54" />
</InvestmentAccount>
<InvestmentAccount Id="Element02_Source2_Sequence004" Type="TransferNonPR" InvestmentStrategyId="Employee" ParameterOverrideIds="AllocationRateOverride">
   <Investment FundName="Fund032" FundValue="11056.71" />
   <Investment FundName="Fund034" FundValue="12401.34" />
   <Investment FundName="Fund035" FundValue="6634.86" />
   <Investment FundName="Fund038" FundValue="16545.00" />
   <Investment FundName="Fund042" FundValue="10036.26" />
   <Investment FundName="fictiousextra" FundValue="1414.00" />
</InvestmentAccount>

I wasn't sure how you wanted the attributes for the grouped InvestmentAccount element, but hopefully you can adjust that yourself.

Answer 2

XSLT 2.0 solution :

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="xs"
    version="2.0">
    <xsl:template match="/">
        <xsl:apply-templates/>
    </xsl:template>
    <xsl:template match="*">
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates/>
        </xsl:copy>
    </xsl:template>
    <xsl:template match="InvestmentAccount[1]">
        <xsl:for-each-group select="self::*|following-sibling::InvestmentAccount" group-by="concat(@InvestmentStrategyId,@Type)" >
            <InvestmentAccount Id="{@Id}" Type="{@Type}" InvestmentStrategyId="{@InvestmentStrategyId}" ParameterOverrideIds="{@ParameterOverrideIds}">
                <xsl:for-each-group select="current-group()/Investment" group-by="@FundName">
                    <Investment FundName="{current-grouping-key()}" FundValue="{sum(for $x in current-group()/@FundValue return xs:double(data($x)))}" />
                </xsl:for-each-group>
            </InvestmentAccount>
        </xsl:for-each-group>
    </xsl:template>
    <xsl:template match="InvestmentAccount"/>
</xsl:stylesheet>

I insert your XML in a root called <test></test> and the result of the XSLT is :

<?xml version="1.0" encoding="UTF-8"?>
<test>
    <InvestmentAccount Id="Element01_Source3_Sequence002" Type="Standard"
        InvestmentStrategyId="Employer" ParameterOverrideIds="AllocationRateOverride">
        <Investment FundName="Fund032" FundValue="4754.82"/>
        <Investment FundName="Fund034" FundValue="4643.48"/>
        <Investment FundName="Fund035" FundValue="2509.46"/>
        <Investment FundName="Fund038" FundValue="7104.71"/>
        <Investment FundName="Fund042" FundValue="4244.08"/>
    </InvestmentAccount>
    <InvestmentAccount Id="Element01_Source4_Sequence003" Type="DWPRebate"
        InvestmentStrategyId="DSS" ParameterOverrideIds="DWPAllocationRateOverride">
        <Investment FundName="Fund032" FundValue="1881.76"/>
        <Investment FundName="Fund034" FundValue="1584.18"/>
        <Investment FundName="Fund035" FundValue="872.99"/>
        <Investment FundName="Fund038" FundValue="2899.53"/>
        <Investment FundName="Fund042" FundValue="1762.62"/>
    </InvestmentAccount>
    <InvestmentAccount Id="Element01_Source2_Sequence001" Type="Standard"
        InvestmentStrategyId="Employee" ParameterOverrideIds="AllocationRateOverride">
        <Investment FundName="Fund032" FundValue="7395.91"/>
        <Investment FundName="Fund034" FundValue="7222.72"/>
        <Investment FundName="Fund035" FundValue="3903.52"/>
        <Investment FundName="Fund038" FundValue="11051.32"/>
        <Investment FundName="Fund042" FundValue="6602.54"/>
    </InvestmentAccount>
    <InvestmentAccount Id="Element02_Source2_Sequence004" Type="TransferNonPR"
        InvestmentStrategyId="Employee" ParameterOverrideIds="AllocationRateOverride">
        <Investment FundName="Fund032" FundValue="11056.71"/>
        <Investment FundName="Fund034" FundValue="12401.34"/>
        <Investment FundName="Fund035" FundValue="6634.860000000001"/>
        <Investment FundName="Fund038" FundValue="16545"/>
        <Investment FundName="Fund042" FundValue="10036.26"/>
        <Investment FundName="fictiousextra" FundValue="1414"/>
    </InvestmentAccount>




</test>

Note : the InvestmentAccount attributes value are the one of the first element InvestmentAccount in the group of InvestmentAccount that has same values for Type and InvestmentStrategyId. This can be changed easily.