[xquery-talk] Xquery : Sort and get only the first record
Christian Grün
christian.gruen at gmail.com
Sun Nov 22 23:43:42 PST 2015
This is one solution, using "empty greatest" (missing dates will be
returned first in the ordered results):
for $job in //job
order by $job/StartDate/text()/xs:date(
replace(., "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2")
) descending empty greatest
count $position
where $position eq 1
return $job
The following one may be more intuitive:
for $job in //job
let $startDate := $job/StartDate/text()
order by (
if($startDate) then (
xs:date(replace($startDate, "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2"))
) else ()
) descending empty greatest
count $position
where $position eq 1
return $job
Cheers,
Christian
______________________________________
On Sun, Nov 22, 2015 at 1:42 AM, sudheshna iyer <sudheshnaiyer at yahoo.com> wrote:
> Currently below works for me:
>
> for $job in //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
>
> To this, I want to add:
>> 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.
>
>
> Thank you for your response.
>
> ________________________________
> 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 15, 2015 4:35 AM
>
> Subject: Re: [xquery-talk] Xquery : Sort and get only the first record
>
> How does your current XQuery expression look like?
>
>
> On Sun, Nov 15, 2015 at 9:11 AM, sudheshna iyer <sudheshnaiyer at yahoo.com>
> wrote:
>> 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
>>
>>
>>
>> _______________________________________________
>> talk at x-query.com
>> http://x-query.com/mailman/listinfo/talk
>
>
More information about the talk
mailing list