<html><head></head><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px"><div id="yui_3_16_0_1_1447574482703_3778"><span id="yui_3_16_0_1_1447574482703_3777">Thank you all for the reply.</span></div><div id="yui_3_16_0_1_1447574482703_3779"><span><br></span></div><div id="yui_3_16_0_1_1447574482703_3782"><span id="yui_3_16_0_1_1447574482703_3781">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. </span></div><div id="yui_3_16_0_1_1447574482703_3782"><span><br></span></div><div id="yui_3_16_0_1_1447574482703_3782"><span id="yui_3_16_0_1_1447574482703_4976">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,,)</span></div><div id="yui_3_16_0_1_1447574482703_3782"><span><br></span></div><div id="yui_3_16_0_1_1447574482703_3782"><span id="yui_3_16_0_1_1447574482703_5346">For eg, in the below case, response should be the job element with id = 1: </span></div><div id="yui_3_16_0_1_1447574482703_3782" class=""><?xml version="1.0" encoding="UTF-8"?></div><div id="yui_3_16_0_1_1447574482703_3782" class=""><Request></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <SessionInfo></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <uid>qq</uid></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <pwd>qq</pwd></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> </SessionInfo></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <Param></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <CustomerInfo></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <Contact></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <Number>123</Number></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <Name>aaa bbb</Name></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <Jobs></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <job></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <Name>Analyst</Name></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <Id>1</Id></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <StartDate></StartDate></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <EndDate>1</EndDate></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> </job></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <job></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <Name>Programmer</Name></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <Id>2</Id></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <StartDate>08-31-2015</StartDate></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> <EndDate>2</EndDate></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> </job></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> </Jobs></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> </Contact></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> </CustomerInfo></div><div id="yui_3_16_0_1_1447574482703_3782" class=""> </Param></div><div id="yui_3_16_0_1_1447574482703_3782"></div><div id="yui_3_16_0_1_1447574482703_3782" dir="ltr" class=""></Request></div><div id="yui_3_16_0_1_1447574482703_3782"><span><br></span></div><div id="yui_3_16_0_1_1447574482703_3782"><span><br></span></div><div id="yui_3_16_0_1_1447574482703_3783"><br></div><br> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;" id="yui_3_16_0_1_1447574482703_3788"> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;" id="yui_3_16_0_1_1447574482703_3787"> <div dir="ltr" id="yui_3_16_0_1_1447574482703_3786"> <hr size="1"> <font size="2" face="Arial" id="yui_3_16_0_1_1447574482703_3785"> <b><span style="font-weight:bold;">From:</span></b> Ghislain Fourny <g@28.io><br> <b><span style="font-weight: bold;">To:</span></b> Ronald Bourret <rpbourret@rpbourret.com> <br><b><span style="font-weight: bold;">Cc:</span></b> "talk@x-query.com" <talk@x-query.com>; sudheshna iyer <sudheshnaiyer@yahoo.com> <br> <b><span style="font-weight: bold;">Sent:</span></b> Tuesday, November 3, 2015 4:51 AM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: [xquery-talk] Xquery : Sort and get only the first record<br> </font> </div> <div class="y_msg_container" id="yui_3_16_0_1_1447574482703_3994"><br><div id="yiv8406340536"><div id="yui_3_16_0_1_1447574482703_3993"><div dir="ltr" id="yui_3_16_0_1_1447574482703_3997"><span style="font-size:12.8px;">Hi Ronald,</span><div id="yui_3_16_0_1_1447574482703_5515"><span style="font-size:12.8px;"><br clear="none"></span></div><div id="yui_3_16_0_1_1447574482703_5517"><span style="font-size:12.8px;" id="yui_3_16_0_1_1447574482703_5516">You can also use a count clause if you have XQuery 3.0! It makes it easier to read. Like so:</span></div><div id="yui_3_16_0_1_1447574482703_4975"><span style="font-size:12.8px;"><br clear="none"></span></div>for $job in doc('x.xml')//job<br clear="none">order by xs:date(replace($job/StartDate, "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2")) descending<br clear="none">count $position<br clear="none">where $position eq 1<br clear="none">return $job<div id="yui_3_16_0_1_1447574482703_5011"><br clear="none"></div><div id="yui_3_16_0_1_1447574482703_3996">I hope it helps.<br clear="none"><div id="yui_3_16_0_1_1447574482703_5012"><br clear="none"></div><div id="yui_3_16_0_1_1447574482703_3998">Kind regards,</div><div id="yui_3_16_0_1_1447574482703_4974">Ghislain</div><div id="yui_3_16_0_1_1447574482703_3995"><br clear="none"></div></div></div><div class="yiv8406340536gmail_extra" id="yui_3_16_0_1_1447574482703_3992"><br clear="none"><div class="qtdSeparateBR"><br><br></div><div class="yiv8406340536yqt1763723886" id="yiv8406340536yqtfd00899"><div class="yiv8406340536gmail_quote" id="yui_3_16_0_1_1447574482703_4203">On Mon, Nov 2, 2015 at 6:27 PM, Ronald Bourret <span dir="ltr"><<a rel="nofollow" shape="rect" ymailto="mailto:rpbourret@rpbourret.com" target="_blank" href="mailto:rpbourret@rpbourret.com">rpbourret@rpbourret.com</a>></span> wrote:<br clear="none"><blockquote class="yiv8406340536gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;" id="yui_3_16_0_1_1447574482703_4202">Can't use just use a predicate that returns the first element in the sorted sequence? Something like:<br clear="none">
<br clear="none">
let $seq = for $job in doc('x.xml')//job<span class="yiv8406340536" id="yui_3_16_0_1_1447574482703_4960"><br clear="none">
order by<br clear="none">
xs:date(replace($job/StartDate,<br clear="none">
"(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2")<br clear="none">
) descending<br clear="none">
return $job<br clear="none"></span>
return $seq[1]<br clear="none">
<br clear="none">
(My apologies if the syntax isn't exact. I haven't written an XQuery for quite a while.)<br clear="none">
<br clear="none">
-- Ron<br clear="none">
<br clear="none">
On 11/2/2015 5:53 AM, sudheshna iyer wrote:<br clear="none">
<blockquote class="yiv8406340536gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;" id="yui_3_16_0_1_1447574482703_4794">
Thank you very much, Christian. Let me try this..<br clear="none">
<br clear="none">
<br clear="none">
<br clear="none">
------------------------------------------------------------------------<br clear="none">
*From:* Christian Grün <<a rel="nofollow" shape="rect" ymailto="mailto:christian.gruen@gmail.com" target="_blank" href="mailto:christian.gruen@gmail.com">christian.gruen@gmail.com</a>><br clear="none">
*To:* sudheshna iyer <<a rel="nofollow" shape="rect" ymailto="mailto:sudheshnaiyer@yahoo.com" target="_blank" href="mailto:sudheshnaiyer@yahoo.com">sudheshnaiyer@yahoo.com</a>><br clear="none">
*Cc:* "<a rel="nofollow" shape="rect" ymailto="mailto:talk@x-query.com" target="_blank" href="mailto:talk@x-query.com">talk@x-query.com</a>" <<a rel="nofollow" shape="rect" ymailto="mailto:talk@x-query.com" target="_blank" href="mailto:talk@x-query.com">talk@x-query.com</a>><br clear="none">
*Sent:* Sunday, November 1, 2015 4:26 PM<br clear="none">
*Subject:* Re: [xquery-talk] Xquery : Sort and get only the first record<span class="yiv8406340536"><br clear="none">
<br clear="none">
If your XQuery processor supports XQuery Update, this would be one solution:<br clear="none">
<br clear="none">
copy $xml := doc('x.xml')<br clear="none">
modify (<br clear="none">
delete node subsequence(<br clear="none">
for $job in $xml//job<br clear="none">
order by xs:date(replace($job/StartDate,<br clear="none">
"(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2")<br clear="none">
) descending<br clear="none">
return $job<br clear="none">
, 2)<br clear="none">
)<br clear="none">
return $xml<br clear="none">
<br clear="none">
Best,<br clear="none">
Christian<br clear="none">
<br clear="none">
<br clear="none">
<br clear="none">
<br clear="none">
On Sun, Nov 1, 2015 at 8:51 PM, sudheshna iyer <<a rel="nofollow" shape="rect" ymailto="mailto:sudheshnaiyer@yahoo.com" target="_blank" href="mailto:sudheshnaiyer@yahoo.com">sudheshnaiyer@yahoo.com</a><br clear="none"></span><div><div class="yiv8406340536h5">
<mailto:<a rel="nofollow" shape="rect" ymailto="mailto:sudheshnaiyer@yahoo.com" target="_blank" href="mailto:sudheshnaiyer@yahoo.com">sudheshnaiyer@yahoo.com</a>>> wrote:<br clear="none">
> Team,<br clear="none">
><br clear="none">
> I have an xml which has multiple jobs elements. I want to sort the<br clear="none">
jobs and<br clear="none">
> want to output only the latest job. Note that <EndDate> can be empty<br clear="none">
> indicating that it is the current job.<br clear="none">
><br clear="none">
> Basically I want to order by jobs/job/EndDate in descending fashion and<br clear="none">
> select only the first record..<br clear="none">
><br clear="none">
> How do I do that using xquery?<br clear="none">
><br clear="none">
> Input request:<br clear="none">
> <?xml version="1.0" encoding="UTF-8"?><br clear="none">
> <Request><br clear="none">
> <SessionInfo><br clear="none">
> <uid>qq</uid><br clear="none">
> <pwd>qq</pwd><br clear="none">
> </SessionInfo><br clear="none">
> <Param><br clear="none">
> <CustomerInfo><br clear="none">
> <Contact><br clear="none">
> <Number>123</Number><br clear="none">
> <Name>aaa bbb</Name><br clear="none">
> <Jobs><br clear="none">
> <job><br clear="none">
> <Name>Analyst</Name><br clear="none">
> <Id>1</Id><br clear="none">
> <StartDate>01-01-2015</StartDate><br clear="none">
> <EndDate>08-30-2015</EndDate><br clear="none">
> </job><br clear="none">
> <job><br clear="none">
> <Name>Programmer</Name><br clear="none">
> <Id>2</Id><br clear="none">
> <StartDate>08-31-2015</StartDate><br clear="none">
> <EndDate /><br clear="none">
> </job><br clear="none">
> </Jobs><br clear="none">
> </Contact><br clear="none">
> </CustomerInfo><br clear="none">
> </Param><br clear="none">
> </Request><br clear="none">
><br clear="none">
> Expected output:<br clear="none">
><br clear="none">
> <?xml version="1.0" encoding="UTF-8"?><br clear="none">
> <Request><br clear="none">
> <SessionInfo><br clear="none">
> <uid>qq</uid><br clear="none">
> <pwd>qq</pwd><br clear="none">
> </SessionInfo><br clear="none">
> <Param><br clear="none">
> <CustomerInfo><br clear="none">
> <Contact><br clear="none">
> <Number>123</Number><br clear="none">
> <Name>aaa bbb</Name><br clear="none">
> <Jobs><br clear="none">
> <job><br clear="none">
> <Name>Programmer</Name><br clear="none">
> <Id>2</Id><br clear="none">
> <StartDate>08-31-2015</StartDate><br clear="none">
> <EndDate/><br clear="none">
> </job><br clear="none">
> </Jobs><br clear="none">
> </Contact><br clear="none">
> </CustomerInfo><br clear="none">
> </Param><br clear="none">
> </Request><br clear="none">
><br clear="none">
> Your help is greatly appreciated.<br clear="none">
<br clear="none">
><br clear="none">
><br clear="none">
><br clear="none">
><br clear="none">
> _______________________________________________<br clear="none"></div></div>
> <a rel="nofollow" shape="rect" ymailto="mailto:talk@x-query.com" target="_blank" href="mailto:talk@x-query.com">talk@x-query.com</a> <mailto:<a rel="nofollow" shape="rect" ymailto="mailto:talk@x-query.com" target="_blank" href="mailto:talk@x-query.com">talk@x-query.com</a>><span class="yiv8406340536"><br clear="none">
> <a rel="nofollow" shape="rect" target="_blank" href="http://x-query.com/mailman/listinfo/talk">http://x-query.com/mailman/listinfo/talk</a><br clear="none">
<br clear="none">
<br clear="none">
<br clear="none">
<br clear="none">
<br clear="none">
_______________________________________________<br clear="none">
<a rel="nofollow" shape="rect" ymailto="mailto:talk@x-query.com" target="_blank" href="mailto:talk@x-query.com">talk@x-query.com</a><br clear="none">
<a rel="nofollow" shape="rect" target="_blank" href="http://x-query.com/mailman/listinfo/talk">http://x-query.com/mailman/listinfo/talk</a><br clear="none">
<br clear="none">
</span></blockquote>
<br clear="none">
---<br clear="none">
This email has been checked for viruses by Avast antivirus software.<br clear="none">
<a rel="nofollow" shape="rect" target="_blank" href="https://www.avast.com/antivirus">https://www.avast.com/antivirus</a><br clear="none">
<br clear="none">
_______________________________________________<br clear="none">
<a rel="nofollow" shape="rect" ymailto="mailto:talk@x-query.com" target="_blank" href="mailto:talk@x-query.com">talk@x-query.com</a><br clear="none">
<a rel="nofollow" shape="rect" target="_blank" href="http://x-query.com/mailman/listinfo/talk">http://x-query.com/mailman/listinfo/talk</a><br clear="none">
</blockquote></div><br clear="none"></div></div></div></div><br><div class="yqt1763723886" id="yqtfd09589">_______________________________________________<br clear="none"><a shape="rect" ymailto="mailto:talk@x-query.com" href="mailto:talk@x-query.com">talk@x-query.com</a><br clear="none"><a shape="rect" href="http://x-query.com/mailman/listinfo/talk" target="_blank">http://x-query.com/mailman/listinfo/talk</a></div><br><br></div> </div> </div> </div></body></html>