[xquery-talk] Xquery : Sort and get only the first record

[Christian Grün]

If your XQuery processor supports XQuery Update, this would be one solution:

  copy $xml := doc('x.xml')
  modify (
    delete node subsequence(
      for $job in $xml//job
      order by xs:date(replace($job/StartDate,
        "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2")
      ) descending
      return $job
    , 2)
  )
  return $xml

Best,
Christian


On Sun, Nov 1, 2015 at 8:51 PM, sudheshna iyer <sudheshnaiyer at yahoo.com> wrote:
> Team,
>
> I have an xml which has multiple jobs elements. I want to sort the jobs and
> want to output only the latest job. Note that <EndDate> can be empty
> indicating that it is the current job.
>
> Basically I want to order by jobs/job/EndDate in descending fashion and
> select only the first record..
>
> How do I do that using xquery?
>
> Input request:
> <?xml version="1.0" encoding="UTF-8"?>
> <Request>
>   <SessionInfo>
>      <uid>qq</uid>
>      <pwd>qq</pwd>
>   </SessionInfo>
>   <Param>
>      <CustomerInfo>
>         <Contact>
>            <Number>123</Number>
>            <Name>aaa bbb</Name>
>            <Jobs>
>               <job>
>                  <Name>Analyst</Name>
>                  <Id>1</Id>
>                  <StartDate>01-01-2015</StartDate>
>                  <EndDate>08-30-2015</EndDate>
>               </job>
>               <job>
>                  <Name>Programmer</Name>
>                  <Id>2</Id>
>                  <StartDate>08-31-2015</StartDate>
>                  <EndDate />
>               </job>
>            </Jobs>
>         </Contact>
>      </CustomerInfo>
>   </Param>
> </Request>
>
> Expected output:
>
> <?xml version="1.0" encoding="UTF-8"?>
> <Request>
>   <SessionInfo>
>      <uid>qq</uid>
>      <pwd>qq</pwd>
>   </SessionInfo>
>   <Param>
>      <CustomerInfo>
>         <Contact>
>            <Number>123</Number>
>            <Name>aaa bbb</Name>
>            <Jobs>
>               <job>
>                  <Name>Programmer</Name>
>                  <Id>2</Id>
>                  <StartDate>08-31-2015</StartDate>
>                  <EndDate/>
>               </job>
>            </Jobs>
>         </Contact>
>      </CustomerInfo>
>   </Param>
> </Request>
>
> Your help is greatly appreciated.
>
>
>
>
> _______________________________________________
> talk at x-query.com
> http://x-query.com/mailman/listinfo/talk