CODEWITHSUNDEEP
xmlxslt-1.0
yashu k
yashu k

Reputation: 3

How to filter and fetch elements in XML which has latest date using XSLT 1.0

I have a XML payload as below, which contains list of accounts. I want to select the Active accounts and if any account is active and is repeating, then i need to select the latest account based on the date of that account.

Input Payload:

<accounts>
    <account1>
        <name>abc</name>
        <account_no>123</account_no>
        <status>Active</status>
        <date>20-05-2018</date>
    </account1>
    <account2>
        <name>def</name>
        <account_no>123</account_no>
        <status>Active</status>
        <date>20-06-2018</date>
    </account2>
    <account3>
        <name>ghi</name>
        <account_no>1234</account_no>
        <status>Active</status>
        <date>20-05-2018</date>
    </account3>
    <account4>
        <name>jkl</name>
        <account_no>1234</account_no>
        <status>In Active</status>
        <date>20-05-2018</date>
    </account4>
</accounts>

Expected result:

<accounts>
    <account2>
        <name>def</name>
        <account_no>123</account_no>
        <status>Active</status>
        <date>20-06-2018</date>
    </account2>
    <account3>
        <name>ghi</name>
        <account_no>1234</account_no>
        <status>Active</status>
        <date>20-05-2018</date>
    </account3>
</accounts>

Please help me with this, i tried using for-each loop using which im able to select the select active plans but not getting how to select the latest account based on date when there is repeating nodes.

Thanks in advance.

Upvotes: 0

Views: 343

Answers (1)

michael.hor257k
michael.hor257k

Reputation: 117073

This requires a considerable amount of work. And your data provider is certainly not making it any easier, with their choice to add a serial number to the account element names, as well as selecting DD-MM-YYYY as the date format.

Try:

XSLT 1.0

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

<xsl:key name='acc' match="*[starts-with(name(), 'account') and status='Active']" use="account_no"/>

<xsl:template match="/accounts">
    <xsl:copy>
        <!-- GROUP ACTIVE ACCOUNTS BY account_no, USING MUENCHIAN GROUPING -->
        <xsl:for-each select="*[starts-with(name(), 'account') and status='Active'][count(. | key('acc', account_no)[1]) = 1]">
            <!-- SORT CURRENT GROUP BY DATE, DESCENDING -->
            <xsl:for-each select="key('acc', account_no)">
                <!-- SORT BY YEAR -->
                <xsl:sort select="substring(date, 7, 4)" data-type="number" order="descending"/>
                <!-- SORT BY MONTH -->
                <xsl:sort select="substring(date, 4, 2)" data-type="number" order="descending"/>
                <!-- SORT BY DAY -->
                <xsl:sort select="substring(date, 1, 2)" data-type="number" order="descending"/>
                <!-- RETURN THE FIRST RECORD IN THE SORTED GROUP -->
                <xsl:if test="position()=1">
                    <xsl:copy-of select="."/>
                </xsl:if>
            </xsl:for-each>
        </xsl:for-each>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>

To understand Muenchian grouping, read: http://www.jenitennison.com/xslt/grouping/muenchian.html

Upvotes: 1

Related Questions