How should i group data in xslt?

Question

I need to group history by period , but i am not able to perform this grouping. can someone please help here. i tried with xsl key but its performing operation only for 1st response. could you please suggest any different approach. is there any approach for grouping as showed in expected output below.

Input

<TEST>
     <RESPONSE>
        <NUMBER>XXXX</NUMBER>
        <HISTORY>
             <Period Year="2013" Month="Apr" Value="77"></Period>
             <Period Year="2013" Month="Mar" Value="99"></Period>
             <Period Year="2013" Month="Feb" Value="88"></Period>
             <Period Year="2012" Month="Jan" Value="11"></Period>
             <Period Year="2012" Month="Mar" Value="22"></Period>
             <Period Year="2011" Month="Apr" Value="444"></Period>
         </HISTORY>
     </RESPONSE>
     <RESPONSE>
        <NUMBER>ZZZZ</NUMBER>
        <HISTORY>
             <Period Year="2016" Month="Jan" Value="999"></Period>
             <Period Year="2016" Month="Mar" Value="454"></Period>
             <Period Year="2015" Month="Dec" Value="234"></Period>
             <Period Year="2014" Month="Jan" Value="767"></Period>
             <Period Year="2014" Month="Sep" Value="667"></Period>
             <Period Year="2013" Month="May" Value="112"></Period>
         </HISTORY>
     </RESPONSE>
</TEST>

Expected output

<TEST>
     <RESPONSE>
        <NUMBER>XXXX</NUMBER>
        <HISTORY>
             <Period Year="2013" Month="Apr" Value="77"></Period>
             <Period Year="2013" Month="Mar" Value="99"></Period>
             <Period Year="2013" Month="Feb" Value="88"></Period>
             <Period Year="2012" Month="Jan" Value="11"></Period>
             <Period Year="2012" Month="Mar" Value="22"></Period>
             <Period Year="2011" Month="Apr" Value="444"></Period>
         </HISTORY>
         <GROUP-HISTORY>
                <YEAR Value="2013">
                        <Months Month="Apr" Value="77"/>
                        <Months Month="Mar" Value="99"/>
                        <Months Month="Feb" Value="88"/>
                </YEAR>
                <YEAR Value="2012">
                        <Months Month="Jan" Value="11"/>
                        <Months Month="Mar" Value="22"/>
                </YEAR>
                <YEAR Value="2011">
                        <Months Month="Apr" Value="444"/>       
                </YEAR>
         </GROUP-HISTORY>
     </RESPONSE>
     <RESPONSE>
        <NUMBER>ZZZZ</NUMBER>
        <HISTORY>
             <Period Year="2016" Month="Jan" Value="999"></Period>
             <Period Year="2016" Month="Mar" Value="454"></Period>
             <Period Year="2015" Month="Dec" Value="234"></Period>
             <Period Year="2014" Month="Jan" Value="767"></Period>
             <Period Year="2014" Month="Sep" Value="667"></Period>
             <Period Year="2013" Month="May" Value="112"></Period>
         </HISTORY>
         <GROUP-HISTORY>
                <YEAR Value="2016">
                        <Months Month="Jan" Value="999"/>
                        <Months Month="Mar" Value="454"/>   
                </YEAR>
                <YEAR Value="2015">
                        <Months Month="Dec" Value="234"/>       
                </YEAR>
                <YEAR Value="2014">
                        <Months Month="Jan" Value="767"/>   
                        <Months Month="Sep" Value="667"/>                               
                </YEAR>
                <YEAR Value="2013">
                        <Months Month="May" Value="112"/>       
                </YEAR>
         </GROUP-HISTORY>
     </RESPONSE>
</TEST>

Sample xslt

<xsl:stylesheet xmlns:xalan="http://xml.apache.org/xalan"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    version="1.0">
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
    <xsl:key name="years" match="/TEST/RESPONSE/HISTORY/Period" use="@Year"/>
    <xsl:template match="TEST">
        <xsl:element name="TEST">
            <xsl:apply-templates select="RESPONSE"/>
        </xsl:element>
    </xsl:template>
    <xsl:template match="node() | @*">
        <xsl:copy>
            <xsl:apply-templates select="node() | @*"/>
        </xsl:copy>
    </xsl:template>
    <xsl:template match="HISTORY">
        <xsl:element name="GROUP-HISTORY">
            <xsl:for-each
                select="/TEST/RESPONSE/HISTORY/Period[generate-id(.) = generate-id(key('years', @Year)[1])]">
                <xsl:sort select="@Year" order="descending"/>
                <xsl:variable name="currY" select="@Year"/>
                <xsl:element name="Year">
                    <xsl:attribute name="Value">
                        <xsl:value-of select="$currY"/>
                    </xsl:attribute>
                    <xsl:for-each select="/TEST/RESPONSE/HISTORY/Period[@Year = $currY]">
                        <xsl:element name="Months">
                            <xsl:attribute name="Month">
                                <xsl:value-of select="@Month"/>
                            </xsl:attribute>
                            <xsl:attribute name="Value">
                                <xsl:value-of select="@Value"/>
                            </xsl:attribute>
                        </xsl:element>
                    </xsl:for-each>
                </xsl:element>
            </xsl:for-each>
        </xsl:element>
    </xsl:template>
</xsl:stylesheet>

Can you please post your XSLT, so that we can correct any issue it has? If you are using `xsl:key` then you may not actually be that for off the solution. Thank you! — Tim C, Apr 03 '19 at 08:11

Tim C · Answer 1 · 2019-04-03T09:04:26.130

The main issue is that you need to account for the NUMBER in your key, otherwise you will group all matching years across the whole of your document

<xsl:key name="years" match="Period" use="concat(../../NUMBER, '|', @Year)"/>

Also, for your first xsl:for-each, you start the select expression with /TEST/RESPONSE/HISTORY/Period, which will also check all Periods in the document, when you really need it to be relative to the current HISTORY, like so:

<xsl:for-each select="Period[generate-id(.) = generate-id(key('years', concat(../../NUMBER, '|', @Year))[1])]">

Try this XSLT

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

    <xsl:key name="years" match="Period" use="concat(../../NUMBER, '|', @Year)"/>

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

    <xsl:template match="HISTORY">
        <xsl:call-template name="identity" />
        <GROUP-HISTORY>
            <xsl:for-each select="Period[generate-id(.) = generate-id(key('years', concat(../../NUMBER, '|', @Year))[1])]">
                <xsl:sort select="@Year" order="descending"/>
                <xsl:variable name="currY" select="@Year"/>
                <Year Value="{$currY}">
                    <xsl:for-each select="key('years', concat(../../NUMBER, '|', $currY))">
                        <Months Month="{@Month}" Value="{@Value}" />
                    </xsl:for-each>
                </Year>
            </xsl:for-each>
        </GROUP-HISTORY>
    </xsl:template>
</xsl:stylesheet>

Do note:

You don't need to specify the full path to the matching element in the xsl:key (not unless you have other elements with the same name but different paths that you don't want to match).
Your template matching TEST was unnecessary, as the identity template would do the same thing.
You don't need to use xsl:element where the name of the element is static. Just write out the element tag directly.
You can use Attribute Value Templates to simplify the creation of attributes.

Hi Tim , thanks for your input but i am getting NUMBER as duplicates for some cases then how should i handle this — Nikhil Karande, Apr 03 '19 at 09:21
You can see it in action at https://xsltfiddle.liberty-development.net/ej9EGcn. Have you added any more templates to the XSLT though? — Tim C, Apr 03 '19 at 09:59
i tried but still facing issue you just replace ZZZZ with XXXX in input and run you will get different output rather than expected one, — Nikhil Karande, Apr 03 '19 at 10:17
Can you have multiple `RESPONSE` elements with the same `NUMBER` in your actual XML then? — Tim C, Apr 03 '19 at 10:36
yes i have multiple RESPONSE elements with the same NUMBER in actual XML — Nikhil Karande, Apr 03 '19 at 11:31
Is https://xsltfiddle.liberty-development.net/ej9EGcn/1 what you are after? — Tim C, Apr 03 '19 at 15:29
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/191206/discussion-between-nikhil-karande-and-tim-c). — Nikhil Karande, Apr 04 '19 at 05:57
Sorry, I am very busy at work now, and so don't have time to engage in discussions :( — Tim C, Apr 04 '19 at 07:56

How should i group data in xslt?

1 Answers1