CODEWITHSUNDEEP
sortingxsltxslt-1.0
Enthusiastic
Enthusiastic

Reputation: 549

Sorting XML data based on date value using XSLT 1.0

I tried to search similar problems and found this and this.. but they don't match my requirement in particular..

Sample XML Input tried with:

<TestMessage>
    <INSTest>
        <INSClaim Id="1-TEST">
            <Id>1-TEST</Id>
            <INSTestElements>
                <INSTestElement>
                    <SortingOrder>2</SortingOrder>
                    <Created>12/29/2012 13:45:58</Created>
                    <Id>1-Element1</Id>
                </INSTestElement>
                <INSTestElement>
                    <SortingOrder>3</SortingOrder>
                    <Created>12/31/2012 14:45:58</Created>
                    <Id>1-Element2</Id>
                </INSTestElement>
                <INSTestElement>
                    <SortingOrder>1</SortingOrder>
                    <Created>12/31/2011 21:45:58</Created>
                    <Id>1-Element3</Id>
                </INSTestElement>
            </INSTestElements>
        </INSClaim>
    </INSTest>
</TestMessage>

XSL input tried with:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="node()|@*">
        <xsl:copy>
            <xsl:apply-templates select="node()|@*"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="INSTestElements">
        <xsl:copy>
            <xsl:for-each select="INSTestElement">
                <xsl:variable name="created"><xsl:value-of select="Created"/></xsl:variable>
                <xsl:variable name="created_date" select="substring-before($created, ' ')"/>
                <xsl:variable name="year" select="substring($created_date, string-length($created_date) -3)"/>
                <xsl:variable name="day" select="substring-before($created_date, '/')"/>
                <xsl:variable name="month" select="format-number(substring-before(substring-after($created_date, $day), $year), '00')"/>
                <xsl:copy>
                    <xsl:apply-templates>
                        <xsl:sort select="$year" data-type="number"/>
                        <xsl:sort select="$month" data-type="number"/>
                        <xsl:sort select="$day" data-type="number"/>
                    </xsl:apply-templates>
                </xsl:copy>
            </xsl:for-each>
        </xsl:copy>
    </xsl:template>
</xsl:stylesheet>

Output I am getting:

<?xml version="1.0" encoding="UTF-8"?>
<TestMessage>
    <INSTest>
        <INSClaim Id="1-TEST">
            <Id>1-TEST</Id>
            <INSTestElements>
                <INSTestElement>
                    <SortingOrder>2</SortingOrder>
                    <Created>12/29/2012 13:45:58</Created>
                    <Id>1-Element1</Id>
                </INSTestElement>
                <INSTestElement>
                    <SortingOrder>3</SortingOrder>
                    <Created>12/31/2012 14:45:58</Created>
                    <Id>1-Element2</Id>
                </INSTestElement>
                <INSTestElement>
                    <SortingOrder>1</SortingOrder>
                    <Created>12/31/2011 21:45:58</Created>
                    <Id>1-Element3</Id>
                </INSTestElement>
            </INSTestElements>
        </INSClaim>
    </INSTest>
</TestMessage>

Expected Output:

<?xml version="1.0" encoding="UTF-8"?>
<TestMessage>
    <INSTest>
        <INSClaim Id="1-TEST">
            <Id>1-TEST</Id>
            <INSTestElements>
                <INSTestElement>
                    <SortingOrder>1</SortingOrder>
                    <Created>12/31/2011 21:45:58</Created>
                    <Id>1-Element3</Id>
                </INSTestElement>
                <INSTestElement>
                    <SortingOrder>2</SortingOrder>
                    <Created>12/29/2012 13:45:58</Created>
                    <Id>1-Element1</Id>
                </INSTestElement>
                <INSTestElement>
                    <SortingOrder>3</SortingOrder>
                    <Created>12/31/2012 14:45:58</Created>
                    <Id>1-Element2</Id>
                </INSTestElement>
            </INSTestElements>
        </INSClaim>
    </INSTest>
</TestMessage>

The mistake I'm doing I guess is, I am trying to apply sort to the child elements of INSTestElement, where as I must have get it applied to INSTestElement itself. I tried shuffling the blocks in the code I have developed, but nothing came fruitful. Rather I found error saying invalid element variable under apply-templates

My main concern is how do I extract the value of date created which I have to use for sorting its very parent INSTestElement..

Upvotes: 1

Views: 4006

Answers (2)

Enthusiastic
Enthusiastic

Reputation: 549

Thanks to Martin for the great tip.. Replaced the template INSTestElements code with below one. Now it sorts based on datetime value of Created field in ascending order.

<xsl:template match="INSTestElements">
    <xsl:copy>
        <xsl:apply-templates select="INSTestElement">
            <xsl:sort select="substring(substring-after(substring-after(Created, '/'), '/'), 1, 4)" data-type="number"/>
            <xsl:sort select="substring(Created, 1, 2)" data-type="number"/>
            <xsl:sort select="substring-before(substring-after(Created, '/'), '/')" data-type="number"/>
            <xsl:sort select="substring-before(substring-after(Created, ' '), ':')"/>
            <xsl:sort select="substring-before(substring-after(Created, ':'), ':')"/>
            <xsl:sort select="substring-after(substring-after(Created, ':'), ':')"/>
        </xsl:apply-templates>
    </xsl:copy>
</xsl:template>

Upvotes: 0

Martin Honnen
Martin Honnen

Reputation: 167571

Change

        <xsl:for-each select="INSTestElement">
            <xsl:variable name="created"><xsl:value-of select="Created"/></xsl:variable>
            <xsl:variable name="created_date" select="substring-before($created, ' ')"/>
            <xsl:variable name="year" select="substring($created_date, string-length($created_date) -3)"/>
            <xsl:variable name="day" select="substring-before($created_date, '/')"/>
            <xsl:variable name="month" select="format-number(substring-before(substring-after($created_date, $day), $year), '00')"/>
            <xsl:copy>
                <xsl:apply-templates>
                    <xsl:sort select="$year" data-type="number"/>
                    <xsl:sort select="$month" data-type="number"/>
                    <xsl:sort select="$day" data-type="number"/>
                </xsl:apply-templates>
            </xsl:copy>
        </xsl:for-each>

to

<xsl:apply-templates select="INSTestElement">
  <xsl:sort select="substring(substring-after(substring-after(Created, '/'), '/'), 1, 4)" data-type="number"/>
  <xsl:sort select="substring(Created, 1, 2)" data-type="number"/>
  <xsl:sort select="substring-before(substring-after(Created, '/'), '/')" data-type="number"/>
</xsl:apply-templates>

Untested but should show the approach to use (i.e. applying templates to those INSTestElement elements and sorting while doing that). The xsl:sort select expressions might need some tuning but as you know the format of the dates in your input it is mainly a question of selecting the right parts.

Upvotes: 1

Related Questions