Re: [xsl] Break functionality in XSL

[Martin Honnen <Martin.Honnen@xxxxxx>]

Anil Kumar Veeramalli wrote:

> this is my sample xml file.
> <TABLE NAME="PS_JOBCODE_TBL">
> <ROWS>
> <ROW>
> <COLUMN NAME="SETID"><![CDATA[COMMN]]></COLUMN>
> <COLUMN NAME="JOBCODE"><![CDATA[P10]]></COLUMN>
> <COLUMN NAME="EFFDT"><![CDATA[01-Jan-1996 00:00:00]]></COLUMN>
> <COLUMN NAME="EFF_STATUS"><![CDATA[A]]></COLUMN>
> <COLUMN NAME="DESCR"><![CDATA[yyyyyyyyyyyyyyy]]></COLUMN>
> </ROW>
> <ROW>
> <COLUMN NAME="SETID"><![CDATA[COMMN]]></COLUMN>
> <COLUMN NAME="JOBCODE"><![CDATA[B64]]></COLUMN>
> <COLUMN NAME="EFFDT"><![CDATA[01-Jan-1900 00:00:00]]></COLUMN>
> <COLUMN NAME="EFF_STATUS"><![CDATA[A]]></COLUMN>
> <COLUMN NAME="DESCR"><![CDATA[CONSTR SUPR]]></COLUMN>
> </ROW>
> <ROW>
> <COLUMN NAME="SETID"><![CDATA[COMMN]]></COLUMN>
> <COLUMN NAME="JOBCODE"><![CDATA[B64]]></COLUMN>
> <COLUMN NAME="EFFDT"><![CDATA[01-Jun-1966 00:00:00]]></COLUMN>
> <COLUMN NAME="EFF_STATUS"><![CDATA[A]]></COLUMN>
> <COLUMN NAME="DESCR"><![CDATA[CONSTR SUPR]]></COLUMN>
> </ROW>
> <ROW>
> <COLUMN NAME="SETID"><![CDATA[COMMN]]></COLUMN>
> <COLUMN NAME="JOBCODE"><![CDATA[P10]]></COLUMN>
> <COLUMN NAME="EFFDT"><![CDATA[16-Jul-1991 00:00:00]]></COLUMN>
> <COLUMN NAME="EFF_STATUS"><![CDATA[A]]></COLUMN>
> <COLUMN NAME="DESCR"><![CDATA[CABLE SPLICERS]]></COLUMN>
> </ROW>
> <ROW>
> <COLUMN NAME="SETID"><![CDATA[COMMN]]></COLUMN>
> <COLUMN NAME="JOBCODE"><![CDATA[P10]]></COLUMN>
> <COLUMN NAME="EFFDT"><![CDATA[16-Jul-1994 00:00:00]]></COLUMN>
> <COLUMN NAME="EFF_STATUS"><![CDATA[A]]></COLUMN>
> <COLUMN NAME="DESCR"><![CDATA[XXXXXXXXXXX]]></COLUMN>
> </ROW>
> </ROWS>
> </TABLE>
>
> I need to find out the DESCR. based on JobCode
>
> *Input :  *Jobcode like P10
> *Process: *find out the all the nodes with  Jobcode  *P10*  and find the 
> latest Effective date (EFFDT) and display corresponding DESCR
> *output: *DESCR with latest Effective date.

You can define a key on the ROW elements:
   <xsl:key name="k1" match="ROW" use="COLUMN[@NAME = 'JOBCODE']"/>
then key('k1', 'P10') gives you a node-set of the ROW elements with that 
key value, then you can sort that by the date in descending order and 
only take the description of the first ROW:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
  xmlns:data="http://example.com/2009/data";
  exclude-result-prefixes="data"
  version="1.0">

  <xsl:param name="jobcode" select="'P10'"/>

  <xsl:key name="k1" match="ROW" use="COLUMN[@NAME = 'JOBCODE']"/>

  <xsl:output method="text"/>

  <data:data xmlns="">
    <month key="Jan" value="01"/>
    <month key="Feb" value="02"/>
    <month key="Mar" value="03"/>
    <month key="Apr" value="04"/>
    <month key="May" value="05"/>
    <month key="Jun" value="06"/>
    <month key="Jul" value="07"/>
    <month key="Aug" value="08"/>
    <month key="Sep" value="09"/>
    <month key="Oct" value="10"/>
    <month key="Nov" value="11"/>
    <month key="Dec" value="12"/>
  </data:data>

  <xsl:template match="/">
    <xsl:for-each select="key('k1', $jobcode)">
      <xsl:sort select="concat(
        substring(COLUMN[@NAME = 'EFFDT'], 8, 4),
        '-',
        document('')/xsl:stylesheet/data:data/month[@key = 
substring(COLUMN[@NAME = 'EFFDT'], 4, 3)]/@value,
        '-',
        substring(COLUMN[@NAME = 'EFFDT'], 1, 2))"
        order="descending"/>
      <xsl:if test="position() = 1">
        <xsl:value-of select="COLUMN[@NAME = 'DESCR']"/>
      </xsl:if>
    </xsl:for-each>
  </xsl:template>

</xsl:stylesheet>


The only problem is dealing with that date format, you can't sort on 
that directly, you need to extract the components and bring them into a 
yyyy-mm-dd format that can be sorted as a string. The stylesheet above 
does that.



--

	Martin Honnen
	http://msmvps.com/blogs/martin_honnen/