[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