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[(_at_)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[(_at_)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[(_at_)NAME = 'EFFDT'], 8, 4),
'-',
document('')/xsl:stylesheet/data:data/month[(_at_)key =
substring(COLUMN[(_at_)NAME = 'EFFDT'], 4, 3)]/@value,
'-',
substring(COLUMN[(_at_)NAME = 'EFFDT'], 1, 2))"
order="descending"/>
<xsl:if test="position() = 1">
<xsl:value-of select="COLUMN[(_at_)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/
--~------------------------------------------------------------------
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: <mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--