date field ordering
Thursday, 20 September 2012 · 2 messages
H
Hariprasad20 Sep 2012 16:30 #
Dear Basex,
Could you please help us in writing xquery for ordering xml based on date field(bDate).For example I have a xml with following nodes
<Results>
<GROUPBYVAL>
<bDate>01-JUN-12</bDate>
</GROUPBYVAL>
<GROUPBYVAL>
<bDate>10-AUG-12</bDate>
</GROUPBYVAL>
<GROUPBYVAL>
<bDate>21-FEB-12</bDate>
</GROUPBYVAL>
</Results>query should take input as the above xml and should return output in the below format
<Results>
<GROUPBYVAL>
<bDate>10-AUG-12</bDate>
</GROUPBYVAL>
<GROUPBYVAL>
<bDate>21-JUL-12</bDate>
</GROUPBYVAL>
<GROUPBYVAL>
<bDate>01-FEB-12</bDate>
</GROUPBYVAL>
</Results>Thanks & Regards
Hariprasad.d
CG
Christian Grün20 Sep 2012 16:42 #
Dear Hariprasad,
I have attached a possible solution below. Note that it may be useful to represent dates in the xs:date format (YYYY-MM-DD) in your data, as this will potentially speed up sorting. Next, the talk at x-query.com list or StackOverflow could be a better target for general questions on XQuery.
Hope this helps,
Christian
__________________________________________
declare variable $MONTHS :=
('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN',
'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC');
let $doc := document{
<Results>
<GROUPBYVAL>
<bDate>01-JUN-12</bDate>
</GROUPBYVAL>
<GROUPBYVAL>
<bDate>10-AUG-12</bDate>
</GROUPBYVAL>
<GROUPBYVAL>
<bDate>21-FEB-12</bDate>
</GROUPBYVAL>
</Results>
}
return <Results>{
for $val in $doc/Results/GROUPBYVAL
order by (
let $date := $val/bDate
let $day := substring($date, 1, 2)
let $month := format-integer(index-of($MONTHS, substring($date, 4,
3)), '00')
let $year := 2000 + number(substring($date, 8, 2))
return xs:date($year || '-' || $month || '-' || $day)
) descending
return $val
}</Results>Quoted text · Hariprasad · 35 lines
On Thu, Sep 20, 2012 at 4:30 PM, Hariprasad <hariprasad.d at intense.in> wrote: > Dear Basex, > > Could you please help us in writing xquery for ordering xml based on date > field(bDate).For example I have a xml with following nodes > > <Results> > <GROUPBYVAL> > <bDate>01-JUN-12</bDate> > </GROUPBYVAL> > <GROUPBYVAL> > <bDate>10-AUG-12</bDate> > </GROUPBYVAL> > <GROUPBYVAL> > <bDate>21-FEB-12</bDate> > </GROUPBYVAL> > </Results> > > query should take input as the above xml and should return output in the > below format > > <Results> > <GROUPBYVAL> > <bDate>10-AUG-12</bDate> > </GROUPBYVAL> > <GROUPBYVAL> > <bDate>21-JUL-12</bDate> > </GROUPBYVAL> > <GROUPBYVAL> > <bDate>01-FEB-12</bDate> > </GROUPBYVAL> > </Results> > > Thanks & Regards > Hariprasad.d
Written for BaseX 7.3. BaseX may have changed since: please check the current documentation.
⚡Generated with XQuery