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

[sudheshna iyer]

Thank you very much, Christian. Let me try this..


      From: Christian Grün <christian.gruen at gmail.com>
 To: sudheshna iyer <sudheshnaiyer at yahoo.com> 
Cc: "talk at x-query.com" <talk at x-query.com> 
 Sent: Sunday, November 1, 2015 4:26 PM
 Subject: Re: [xquery-talk] Xquery : Sort and get only the first record
   
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


  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://x-query.com/pipermail/talk/attachments/20151102/0c1eea51/attachment.html>