limit the rows - XMLAgg, group by, rownum
575384May 1 2007 — edited May 2 2007I am using Oracle XML functions to retrieve data. I am using XMLAgg() funtion with a group by clause. The trouble is when I try to limit the number of rows.
I can limit the rows in a regular query using rownum by
select * from ( select t.name from employee t) where rownum < 5
But the same principle is not working when I apply it to an xml query. Can some one explain me the correct way?
For example, some thing like the following fails ( say I would like to group the number of employees in a department) -
xmlelement(name "awb:employees",
(select * from (select distinct xmlagg(
xmlelement(name "awb:employee",
xmlelement(name "awb:count", count(*)),
xmlelement(name "awb:department", e.departmentName)
) FROM employee e
group by e.departmentName
) where rownum < 5)
)