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

[sudheshna iyer]

Thank you all for the reply.
Just need one more modification to this.  In the input if the StartDate is blank, then I should consider that as the current job and return the job that has blank as the output. 
If there are no jobs which have startDate as blank, then it should return the recent startdate job (for which we already have the response as shown in email chain,,)
For eg, in the below case, response should be the job element with id = 1: <?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></StartDate>                  <EndDate>1</EndDate>               </job>               <job>                  <Name>Programmer</Name>                  <Id>2</Id>                  <StartDate>08-31-2015</StartDate>                 <EndDate>2</EndDate>               </job>            </Jobs>         </Contact>      </CustomerInfo>   </Param></Request>



      From: Ghislain Fourny <g at 28.io>
 To: Ronald Bourret <rpbourret at rpbourret.com> 
Cc: "talk at x-query.com" <talk at x-query.com>; sudheshna iyer <sudheshnaiyer at yahoo.com> 
 Sent: Tuesday, November 3, 2015 4:51 AM
 Subject: Re: [xquery-talk] Xquery : Sort and get only the first record
   
Hi Ronald,
You can also use a count clause if you have XQuery 3.0! It makes it easier to read. Like so:
for $job in doc('x.xml')//job
order by xs:date(replace($job/StartDate, "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2")) descending
count $position
where $position eq 1
return $job
I hope it helps.

Kind regards,Ghislain



On Mon, Nov 2, 2015 at 6:27 PM, Ronald Bourret <rpbourret at rpbourret.com> wrote:

Can't use just use a predicate that returns the first element in the sorted sequence? Something like:

  let $seq = for $job in doc('x.xml')//job
             order by
                xs:date(replace($job/StartDate,
                        "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2")
                       ) descending
             return $job
  return $seq[1]

(My apologies if the syntax isn't exact. I haven't written an XQuery for quite a while.)

-- Ron

On 11/2/2015 5:53 AM, sudheshna iyer wrote:

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
<mailto: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 <mailto:talk at x-query.com>
 > http://x-query.com/mailman/listinfo/talk





_______________________________________________
talk at x-query.com
http://x-query.com/mailman/listinfo/talk



---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

_______________________________________________
talk at x-query.com
http://x-query.com/mailman/listinfo/talk



_______________________________________________
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/20151115/3d4c8f06/attachment.html>