Get most recent date in xml records

Question

My data model is as follows:

<Club>
<Captain>
<Name></Name>
<DateOfBirth>15-01-1985</DateOfBirth>
</Captain>
<PlayingStaff>
<Player>
<DateOfBirth>14-01-1993</DateOfBirth>
</Player>
<Player>
<DateOfBirth>07-12-1975</DateOfBirth>
</Player>
<Player>
<DateOfBirth>11-11-1991</DateOfBirth>
</Player>
</PlayingStaff>
</Club>

I've tried using the answer given here: XSLT: Getting the latest date but it isnt giving me any value.

I'm trying to get the youngest player to pass to an external function.

I'm doing this in Biztalk so I have to stick to XSLT1

My work so far is as follows:

<xsl:variable name="youngestPlayer">
            <xsl:for-each select="$ClubRoot/*[local-name()='PlayingStaff']/*[local-name()='Player']">
                <xsl:sort select="./*[local-name()='DateOfBirth']" order="descending"/>
                <xsl:if test="position() = 1">
                    <xsl:value-of select="DateOfBirth"/>
                </xsl:if>
            </xsl:for-each>
        </xsl:variable>
        <xsl:variable name="IsYoungestPlayerUnderAgeLimit" select="externalfunctionreturningboolean">
            <xsl:element name="blahhh"><xsl:value-of select="$IsYoungestPlayerUnderAgeLimit"/></xsl:element>
            <xsl:element name="blahhh"><xsl:value-of select="$youngestPlayer"/></xsl:element>

This is part of a large template - I can't really change this, but the value of ClubRoot is "<xsl:variable name="ClubRoot" select="/*[1]"/>" to ensure I can read its child nodes.

I'm always getting

<blahhh>false</blahhh>
<blahhh/>

as my debug values... so i'm not picking up the value I expect

Can someone highlight where I've gone wrong?

From the data above, I'd expect the value of 14-01-1993 in my youngestPlayer variable. But its blank.

score 7 · Accepted Answer · answered Oct 01 '12 at 15:52

The problem is that XSLT1.0 doesn't really have the concept of dates, so you are effectively sorting by the DateOfBirth elements as if they were just normal strings. If you can be sure the dates always come in the format DD-MM-YYYY you could use string manipulation to sort by the year, month and day

<xsl:sort select="number(substring(DateOfBirth, 7, 4))" order="descending"/>
<xsl:sort select="number(substring(DateOfBirth, 3, 2))" order="descending"/>
<xsl:sort select="number(substring(DateOfBirth, 1, 2))" order="descending"/>

So, given the following XSLT

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="xml" indent="yes"/>
   <xsl:template match="/Club">
      <xsl:for-each select="PlayingStaff/Player">
         <xsl:sort select="number(substring(DateOfBirth, 7, 4))" order="descending"/>
         <xsl:sort select="number(substring(DateOfBirth, 3, 2))" order="descending"/>
         <xsl:sort select="number(substring(DateOfBirth, 1, 2))" order="descending"/>

         <xsl:if test="position() = 1">
            <xsl:value-of select="DateOfBirth"/>
         </xsl:if>
      </xsl:for-each>
   </xsl:template>
</xsl:stylesheet>

When applied to your XML, the following is output

14-01-1993

By using the translate() function, you can reduce the count of xsl:sort instructions from 3 to 1, AND simplify the `xsl:sort/@select expression` . — Sean B. Durkin, Oct 01 '12 at 16:07

score 2 · Answer 2 · edited May 23 '17 at 12:15

The reason why the date sorting in your linked reference 'worked' was because it was in yyyy-MM-dd format, as opposed to your dd-MM-yyyy format.

An alternative to Tim C / Sean's proposal is to use C# script functions (since you are using BizTalk) to revert the date to a sortable one as per your link - but note that this is unlikely to be as performant as native xslt functions. Note also that you may need to use msxsl:node-set on your variables to tell BizTalk's parser that this is a fragment .

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:userCSharp="http://schemas.microsoft.com/BizTalk/2003/userCSharp"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                exclude-result-prefixes="userCSharp msxsl"
                >
    <xsl:output method="xml" indent="yes"/>

    <xsl:template match="/">
        <xsl:variable name="ClubRoot" select="/*[1]"/>
        <xsl:variable name="orderedPlayers">
            <xsl:for-each select="msxsl:node-set($ClubRoot)/*[local-name()='PlayingStaff']/*[local-name()='Player']">
                <xsl:sort select="userCSharp:makeSortableDate(string(*[local-name()='DateOfBirth']), 'dd-MM-yyyy')" order="descending"/>
                <xsl:copy-of select="node() | @*"/>
            </xsl:for-each>
        </xsl:variable>

        <xsl:variable name="youngestPlayerDOB">
            <xsl:value-of select="msxsl:node-set($orderedPlayers)[1]/DateOfBirth/text()" />
        </xsl:variable>

        <xsl:element name="blahhh">
            <xsl:variable name="IsYoungestPlayerUnderAgeLimit" select="userCSharp:externalfunctionreturningboolean($youngestPlayerDOB)" />
            <xsl:value-of select="$IsYoungestPlayerUnderAgeLimit"/>
        </xsl:element>
        <xsl:element name="blahhh">
            <xsl:value-of select="$youngestPlayerDOB"/>
        </xsl:element>
    </xsl:template>

    <msxsl:script language="C#" implements-prefix="userCSharp">
        <![CDATA[
        public System.String makeSortableDate(System.String yourDate, string format)
        {
            return (System.DateTime.ParseExact(yourDate, format, System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd"));
        }

        public bool externalfunctionreturningboolean(System.String dobString)
        {
            System.DateTime someDate;
            if (System.DateTime.TryParse(dobString, out someDate))
            {
                // NB : Doesn't work out leap years correctly!
                if ((System.DateTime.Now - someDate).Days < 21 * 365.25)
                {
                    return true;
                }
            }
            return false;
        }
    ]]>
    </msxsl:script>

</xsl:stylesheet>

I've taken a hack at function and guessed that the underage limit is 21. The above returns

<blahhh>true</blahhh>
<blahhh>14-01-1993</blahhh>

Get most recent date in xml records

2 Answers2