XMLAgg and order by
Hello;
I can't seem to figure out how to use the "order by" syntax of the XMLAgg() function - wondering if someone has a short example they could share.
As specified in the syntax diagram for XMLAgg()(http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96620/xdb12gen.htm#1027574), it should be possible to specify an order by clause that lets me sort the nested elements.
Every variation I've tried though doesn't let me do it. Here's a copy/paste of example 10-14 of the above referenced Oracle manual. What I want to do essentially is specify that the nested <emp> elements are sorted by e.name in the produced xml, and that similarly, the <dependent> elements are sorted by de.name.
Any/all help is appreciated.
Regards,
Brian.
SELECT XMLELEMENT( "Department", XMLATTRIBUTES ( d.dname AS "name" ),
(SELECT XMLAGG(XMLELEMENT ("emp", XMLATTRIBUTES (e.ename AS name),
( SELECT XMLAGG(XMLELEMENT( "dependent",
XMLATTRIBUTES(de.name AS "name")))
FROM dependents de
WHERE de.empno = e.empno ) ))
FROM emp e
WHERE e.deptno = d.deptno) ) AS "dept_list"
FROM dept d ;
<Department name="Accounting">
<emp name="Smith">
<dependent name="Sara Smith"/d>
<dependent name="Joyce Smith"/>
</emp>
<emp name="Yates"/>
</Department>
<Department name="Shipping">
<emp name="Martin">
<dependent name="Alan Martin"/>
</emp>
<emp name="Oppenheimer">
<dependent name="Ellen Oppenheimer"/>
</emp>
</Department>