Summation of nodes by grouping in XSLT

Question

I need to sum the values of few of the nodes which have common ID.

My input XML is like following:

<?xml version="1.0" encoding="utf-8" standalone="no"?>
<Rowsets CachedTime="" DateCreated="2014-05-01T13:11:42" EndDate="2014-05-01T13:11:42" StartDate="2014-05-01T12:11:42" Version="14.0 SP4 Patch 0 (Nov 22, 2013)">
    <Rowset>
        <Columns>
            <Column Description="ID" MaxRange="1" MinRange="0" Name="ID" SQLDataType="12" SourceColumn="ID"/>
            <Column Description="Name" MaxRange="1" MinRange="0" Name="Name" SQLDataType="12" SourceColumn="Name"/>
            <Column Description="ProductCode" MaxRange="1" MinRange="0" Name="ProductCode" SQLDataType="1" SourceColumn="ProductCode"/>
            <Column Description="NoOfMachine" MaxRange="1" MinRange="0" Name="NoOfMachine" SQLDataType="1" SourceColumn="NoOfMachine"/>
            <Column Description="MPerHour" MaxRange="1" MinRange="0" Name="MPerHour" SQLDataType="3" SourceColumn="MPerHour"/>
            <Column Description="TargetProduction" MaxRange="1" MinRange="0" Name="TargetProduction" SQLDataType="3" SourceColumn="TargetProduction"/>
            <Column Description="ActualProduction" MaxRange="1" MinRange="0" Name="ActualProduction" SQLDataType="3" SourceColumn="ActualProduction"/>
        </Columns>
        <Row>
            <ID>S111</ID>
            <Name/>
            <ActualHours>25</ActualHours>
            <ProductCode>16J16</ProductCode>
            <NoOfMachine>5</NoOfMachine>
            <MPerHour>10</MPerHour>
            <TargetProduction>225.50</TargetProduction>
            <ActualProduction>300.75</ActualProduction>
        </Row>
        <Row>
            <ID>S111</ID>
            <Name/>
            <ActualHours>20</ActualHours>
            <ProductCode>16J16</ProductCode>
            <NoOfMachine>2</NoOfMachine>
            <MPerHour>10</MPerHour>
            <TargetProduction>24.50</TargetProduction>
            <ActualProduction>1.25</ActualProduction>
        </Row>
        <Row>
            <ID>S112</ID>
            <Name/>
            <ActualHours>25</ActualHours>
            <ProductCode>16J26</ProductCode>
            <NoOfMachine>5</NoOfMachine>
            <MPerHour>10</MPerHour>
            <TargetProduction>225.50</TargetProduction>
            <ActualProduction>300.75</ActualProduction>
        </Row>
        <Row>
            <ID>S111</ID>
            <Name/>
            <ActualHours>5</ActualHours>
            <ProductCode>16J16</ProductCode>
            <NoOfMachine>1</NoOfMachine>
            <MPerHour>10</MPerHour>
            <TargetProduction>5</TargetProduction>
            <ActualProduction>300</ActualProduction>
        </Row>
    </Rowset>
</Rowsets>

I want to sum up values of <ActualHours> , <NoOfMachines> , <MPerHour> , <TargetProduction> and <ActualProduction> for common ID.

Hence output XML will be like following:

<?xml version="1.0" encoding="utf-8" standalone="no"?>
<Rowsets CachedTime="" DateCreated="2014-05-01T13:11:42" EndDate="2014-05-01T13:11:42" StartDate="2014-05-01T12:11:42" Version="14.0 SP4 Patch 0 (Nov 22, 2013)">
    <Rowset>
        <Columns>
            <Column Description="ID" MaxRange="1" MinRange="0" Name="ID" SQLDataType="12" SourceColumn="ID"/>
            <Column Description="Name" MaxRange="1" MinRange="0" Name="Name" SQLDataType="12" SourceColumn="Name"/>
            <Column Description="ProductCode" MaxRange="1" MinRange="0" Name="ProductCode" SQLDataType="1" SourceColumn="ProductCode"/>
            <Column Description="NoOfMachine" MaxRange="1" MinRange="0" Name="NoOfMachine" SQLDataType="1" SourceColumn="NoOfMachine"/>
            <Column Description="MPerHour" MaxRange="1" MinRange="0" Name="MPerHour" SQLDataType="3" SourceColumn="MPerHour"/>
            <Column Description="TargetProduction" MaxRange="1" MinRange="0" Name="TargetProduction" SQLDataType="3" SourceColumn="TargetProduction"/>
            <Column Description="ActualProduction" MaxRange="1" MinRange="0" Name="ActualProduction" SQLDataType="3" SourceColumn="ActualProduction"/>
        </Columns>
        <Row>
            <ID>S111</ID>
            <Name/>
            <ActualHours>50</ActualHours>
            <ProductCode>16J16</ProductCode>
            <NoOfMachine>8</NoOfMachine>
            <MPerHour>30</MPerHour>
            <TargetProduction>255</TargetProduction>
            <ActualProduction>602</ActualProduction>
        </Row>
        <Row>
            <ID>S112</ID>
            <Name/>
            <ActualHours>25</ActualHours>
            <ProductCode>16J26</ProductCode>
            <NoOfMachine>5</NoOfMachine>
            <MPerHour>10</MPerHour>
            <TargetProduction>225.50</TargetProduction>
            <ActualProduction>300.75</ActualProduction>
        </Row>
    </Rowset>
</Rowsets>

Can anyone help me how can i achieve this through xslt.

Answer 1

You can use a key to select the nodes and group them by ID:

 <xsl:key name="rows" match="Row" use="ID"/>

Then select each similar node only once, comparing to the key:

<xsl:template match="Rowset">
    <xsl:copy>
        <xsl:apply-templates select="Row[generate-id(.) = generate-id(key('rows', ID))]"/>
    </xsl:copy>
</xsl:template>

To sum all the values you can use a XPath expression for each node you wish to sum:

sum(//Row[ID='S112']/ActualHours)

and use that in a template for each different ID.

Edit: a better solution (as suggested by @IanRoberts) is using the key we generated and selecting each based on the ID of the current node being processed:

sum(key('rows', ID)/ActualHours)

(The example above is applied in a Row context.)

Here is a full stylesheet that uses these transformations and that you can use as a starting point:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>
    <xsl:key name="rows" match="Row" use="ID"/>

    <xsl:template match="Rowset">
        <xsl:copy>
            <xsl:apply-templates select="Row[generate-id(.) = generate-id(key('rows', ID))]"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="Row">
        <xsl:copy>
            <xsl:apply-templates/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="ActualHours | NoOfMachine | MPerHour | TargetProduction | ActualProduction">
        <xsl:variable name="tag-name" select="name()"/>
        <xsl:copy>
            <xsl:value-of select="sum(key('rows', ../ID)/*[$tag-name=name()])"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="Name | ID | ProductCode">
        <xsl:copy>
            <xsl:value-of select="."/>
        </xsl:copy>
    </xsl:template>

</xsl:stylesheet>