XSLT Sort grandchild nodes and pick the value of another grandchild

Question

I am trying to figure out the XSLT (CSV output) for the below XML.
I would like to sort the grandchildren nodes i.e. sort Month node and pick the Sales_Program-ID node value of the set with the highest month value.

XML

<Root>
    <Level1>
        <EMPLID>123</EMPLID>
        <Program>
            <Sales_Program Name="XYZ">
                <ID1>ab</ID1>
            </Sales_Program>
            <Start_Date>Jan1st</Start_Date>
            **<Month>1</Month>**
        </Program>
        <Program>
            <Sales_Program Name="ABC">
                <ID1>cd</ID1>
            </Sales_Program>
        <Start_Date>Feb1</Start_Date>
        **<Month>2</Month>**
        </Program>
    </Level1>
    <Level1>
        <EMPLID>456</EMPLID>
        <Program>
            <Sales_Program Name="XYZ">
                <ID1>ab</ID1>
            </Sales_Program>
            <Start_Date>Jan1st</Start_Date>
            <Month>1</Month>
        </Program>
    </Level1>
</Root>

Expected Output:

123,ab,Feb1,2 - (From first Level1 Node)
456,cd,Jan1st,1  (From second Level1 Node)

Answer 1

So you want to sort the Program elements by the Month child, in XSLT 3 with support for the higher-order sort function you can do that with

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:math="http://www.w3.org/2005/xpath-functions/math"
    exclude-result-prefixes="xs math"
    version="3.0">

    <xsl:output method="text"/>

    <xsl:template match="/">
        <xsl:apply-templates select="Root/Level1"/>
    </xsl:template>

    <xsl:template match="Level1">
        <xsl:value-of select="EMPLID, sort(Program, (), function($p) { -$p/Month/xs:integer(.) })[1]/(Sales_Program/ID1, Start_Date, Month)" separator=","/>
        <xsl:text>&#10;</xsl:text>
    </xsl:template>

</xsl:stylesheet>

in XSLT 2 you need to implement the sorting in your own function with xsl:perform-sort:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:mf="http://example.com/mf"
    exclude-result-prefixes="xs mf"
    version="2.0">

    <xsl:output method="text"/>

    <xsl:function name="mf:sort">
        <xsl:param name="programs" as="element(Program)*"/>
        <xsl:perform-sort select="$programs">
            <xsl:sort select="xs:integer(Month)" order="descending"/>
        </xsl:perform-sort>
    </xsl:function>

    <xsl:template match="/">
        <xsl:apply-templates select="Root/Level1"/>
    </xsl:template>

    <xsl:template match="Level1">
        <xsl:value-of select="EMPLID, mf:sort(Program)[1]/(Sales_Program/ID1, Start_Date, Month)" separator=","/>
        <xsl:text>&#10;</xsl:text>
    </xsl:template>

</xsl:stylesheet>