Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

limit the rows - XMLAgg, group by, rownum

575384May 1 2007 — edited May 2 2007
I 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)
)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 30 2007
Added on May 1 2007
2 comments
1,040 views