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

Ghislain Fourny g at 28.io
Tue Nov 3 01:51:25 PST 2015


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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://x-query.com/pipermail/talk/attachments/20151103/75f449c8/attachment.html>


More information about the talk mailing list