Identifying the latest node using end_date field values using xslt

Question

I have a requirement to identify the latest record from the child node based on the end_date value. As per the source XML data, end_date can be missing (not exist) or will be having values. I need to pick the records based on this order /condition

a) if end_date field is missing (any one of the nodes) then should pick those records. b) if end_date is filled (for all nodes) then need to pick the highest (latest date )

tried with the below code but its works only when end_date is existing and skipping those missing the records which having no end_Date.

<xsl:for-each select="hm:job_information">
  <xsl:sort select="hm:end_date" data-type="text" order="descending" />
  <xsl:if test="position()=1">
    <xsl:text>"business_unit":"</xsl:text> <xsl:value-of select="./hm:business_unit" />
    <xsl:text>"company":"</xsl:text> <xsl:value-of select="./hm:company" />
  </xsl:if>
 </xsl:for-each>

The below is the sample input file. i would like to record with 80004122 (as business_unit) as this records doesnt have any end date . If suppose both records (80004122,70001634) having end date then need to pick the one which has latest end date in it.

<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
   <S:Body>
  <queryResponse xmlns="urn:sfobject.sfapi.successfactors.com" xmlns:ns2="urn:fault.sfapi.successfactors.com">
     <result>
        <sfobject>
           <id>18348</id>
           <type>CompoundEmployee</type>
           <person>
            <user_id>28009483</user_id>
                 <personal_information>
                     <first_name>Rodrigo</first_name>
                     <first_name_alt1>Rodrigo</first_name_alt1>
                     <formal_name>Rodrigo Lacey</formal_name>
                     <gender>M</gender>
                  </personal_information>   
                  <employment_information>
                      <job_information>
                        <business_unit>100</business_unit>
                        <company>11</company>
                        <start_date>2010-09-01</start_date>
                        <end_date>2013-08-01</end_date>
                      </job_information>
                      <job_information>
                        <business_unit>200</business_unit>
                        <company>22</company>
                        <start_date>2020-03-11</start_date>
                        <end_date>2021-08-01</end_date>
                      </job_information>
                      <job_information>
                        <business_unit>300</business_unit>
                        <company>33</company>
                        <start_date>2015-03-11</start_date>
                        <end_date>2016-10-01</end_date>
                      </job_information>
                </employment_information>
          </person>
           <execution_timestamp>2022-03-03T02:31:15.000Z</execution_timestamp>
           <version_id>2111P0</version_id>
        </sfobject>
        <numResults>1</numResults>
        <hasMore>false</hasMore>
        <querySessionId>c2172238-c6ed-4fd5-aa9a-6840a2fd1b26</querySessionId>
     </result>
  </queryResponse>
   </S:Body>
</S:Envelope>

michael.hor257k · Accepted Answer · 2022-03-02 09:53:14Z

0

Consider this example:

XSLT 2.0

<xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
exclude-result-prefixes="xs">

<xsl:template match="employment_information">
    <xsl:variable name="max-date" select="max(job_information/xs:date(end_date))" />
    <xsl:variable name="last-job" select="(job_information[not(end_date)], job_information[end_date=$max-date])[1]" />
    <xsl:value-of select="$last-job/business_unit"/>
</xsl:template>

</xsl:stylesheet>

Added:

Can i do anything on the sort part ?

Yes, you could sort as follows:

<xsl:sort select="number(boolean(end_date))" data-type="number" order="ascending" />
<xsl:sort select="end_date" data-type="text" order="descending" />

edited Mar 2, 2022 at 9:53

answered Mar 2, 2022 at 9:12

michael.hor257k

118k6 gold badges36 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sumesh Over a year ago

thanks michael for the example will try it out , i just also want to double check on the sorting approach. Can i do anything on the sort part ?<xsl:sort select="hm:end_date" data-type="text" order="descending"/> or its not an ideal approach. Im little knowledge in xslt

michael.hor257k Over a year ago

See the addition to my post.

Sumesh Over a year ago

Michael sorry for bothering again , when i tested second scenario. b) if end_date is filled (for all nodes) then need to pick highest (latest date ) is not working currectly . As per my understanding the solution above it will mark 0 when no end_date filed in the node and otherwise it set 1 . In the second sort it will sort based o and 1 ? is that my understanding is correct

michael.hor257k Over a year ago

It works for me - see: xsltfiddle.liberty-development.net/aB9NKW.

Sumesh Over a year ago

Thanks Micheal , The provide link working fine and that exactly what i am looking for. My be i was not integrated correctly with my scripts , Will double check and update you soon . thanks for the helps again

|

Collectives™ on Stack Overflow

Identifying the latest node using end_date field values using xslt

1 Answer 1

Added:

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Added:

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related