XPATH 1.0 XSLT 1.0 Return elements or attributes based upon a max date

by | Sep 25, 2009 | BizTalk Community Blogs via Syndication | 0 comments

While working for a client we had to use some custom XSLT within BizTalk to complete some requirements that was outside the bounds of the BizTalk Mapper and orchestrations. Along with what we were already doing the client came to us and wanted a value assigned to an attribute based up on the most recent input file that had been dropped so essentially based upon the a max date from an array of dates. Come to find out that XPATH 1.0 and XSLT 1.0 can’t select records based upon a max date. Here is how I solved this problem.

I made some generic samples to help explain. Here is a sample input document. 



<?xml version="1.0" encoding="utf-8"?>
<BatchedFile>
  <Record>
    <InformationElement Date="12/09/2008" ID="TryAgain" />
  </Record>
  <Record>
    <InformationElement Date="11/11/2008" ID="TryAgaine" />
  </Record>
  <Record>
    <InformationElement Date="01/08/2008" ID="TryAgain" />
  </Record>
  <Record>
    <InformationElement Date="03/09/2008" ID="TryAgain" />
  </Record>
  <!-- I Want to pick the following record ID because it is the most recent date- -->

  <Record>
    <InformationElement Date="01/09/2009" ID="YouPickedTheRightOne" />
  </Record>
  <Record>
    <InformationElement Date="07/09/2008" ID="TryAgain" />
  </Record>
  <Record>
    <InformationElement Date="12/09/2008" ID="TryAgain" />
  </Record>  
</BatchedFile>

The trick is to try and compare the dates. XSLT can’t make an array exactly but you can use variables in a similar way and use the node-set() function to select the values within the variable. 

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

    <xsl:template match="BatchedFile">
      <!-- First Create a variable that stores the Date Attribute along with its corresponding absolute value-->
      <xsl:variable name="MaxDateRecord">
        <xsl:for-each select ="./Record/InformationElement/@Date">
          <TotalDays>
            <xsl:attribute name ="RecordDate">
              <xsl:value-of select ="self::node()"/>
            </xsl:attribute>
            <!--Please see below for explanation of totalDays function-->
            <xsl:value-of select ="userCSharp:totalDays(self::node())"/>            
          </TotalDays>          
        </xsl:for-each>       
      </xsl:variable>
      <!--I decided to create a variable that stored the most recent date, its easier to debug-->
      <xsl:variable name ="MostRecentDate">
        <!--The predicate after TotalDays is what picks the highest number value, which then corresponds to a date-->
        <xsl:value-of select ="msxsl:node-set($MaxDateRecord)/TotalDays[not(. &lt; preceding-sibling::TotalDays or . &lt; following-sibling::TotalDays)]/@RecordDate"/>
      </xsl:variable>
      <!-- Here is where the output is generated, -->
      <MaxDate>
        <xsl:attribute name="DidIPickTheRightOne">
          <xsl:value-of select ="//InformationElement[@Date=$MostRecentDate]/@ID"/>
        </xsl:attribute>
      </MaxDate>
    </xsl:template>
  <msxsl:script language="C#" implements-prefix="userCSharp">
    <!-- This function takes a date like 09/01/2008 
    and returns the number days that exist between 01/01/1990 
    and the current date selected. Any date would work as a reference
    date.-->
    <![CDATA[
 public static string totalDays(string date)
        {
            DateTime d1 = new DateTime();
            DateTime d2 = new DateTime(1990, 01, 01);
            TimeSpan span = new TimeSpan();
            DateTime.TryParse(date,out d1);            
            span = d1 - d2;
            return span.TotalDays.ToString();
            
        }       
]]>
  </msxsl:script>
</xsl:stylesheet>

Okay here is an explanation of what is going on.

1. I first create a variable (MaxDateRecord) that stores the date value of each record as well as the number of days between that date and Jan 1, 1990. This gives me a numeric value that XSLT can compare.

a. Here is what MaxDateRecord would look like essentially after it had ran through all the iterations of the Record element.

<MaxDateRecord>
<TotalDays RecordDate="12/09/2008">6917</TotalDays>
<TotalDays RecordDate="11/11/2008">6889</TotalDays>
<TotalDays RecordDate="01/08/2008">6581</TotalDays>
<TotalDays RecordDate="03/09/2008">6642</TotalDays>
<TotalDays RecordDate="01/09/2009">6948</TotalDays>
<TotalDays RecordDate="07/09/2008">6764</TotalDays>
<TotalDays RecordDate="12/09/2008">6917</TotalDays>
</MaxDateRecord>

2. I then compare all of the TotalDays and find the largest one and select out the RecordDate that matches.

a. The MostRecentDate variable outputs “01/09/2009” in Visual Studio.

3. After that its an easy process of selecting the Record element with the corresponding MostRecentDate or in this case the ID of that element. 

<?xml version="1.0" encoding="utf-8"?>
<!-- This is what my output should be -->
<MaxDate DidIPickTheRightOne="YouPickedTheRightOne" />

I hope I have saved some of you some time. Since BizTalk 2009 still uses the 1.0 specification of XSLT and XPath, I was somewhat disappointed to find out there was no direct way to get this done. I know there are better ways to handle the logic I wrote in XSLT so if you have suggestions or criticisms, post them.

I have to give some thanks to Jirka Kosek for the article “Understanding the node-set() Function” http://www.xml.com/pub/a/2003/07/16/nodeset.html

as well as lhaeger for the article “Again! The power of XPATH or the missing max() function” http://www.novell.com/communities/node/6431/again-power-xpath-or-missing-max-function

Submit a Comment