This content has been marked as final. Show 1 reply
The reason you are only getting the DBA roll back is because you are aggregating all the rolls with the initial XMLAGG. you should remove the fist XMLAGG and then add a group by clause at the end.
I have taken the liberty of re-writing your query and cleaning up the XML. This should get you what you want.
SELECT (XMLElement("ROLE", XMLATTRIBUTES(A.ROLE AS NAME),
(SELECT XMLAgg(XMLELement("PRIVILEGE", XMLATTRIBUTES(B.PRIVILEGE as NAME)))FROM dba_sys_privs B
Where a.role = b.grantee
FROM DBA_ROLES A JOIN dba_sys_privs B ON A.ROLE = B.GRANTEE
GROUP BY A.ROLE ORDER BY A.ROLE;
Hope that helps!