Hi All, I am tyring to return a dataset to XML. I am attempting to get a list of ALL the roles in my database, and then all the privileges assigned to each role. The issue i seem to be running into is that the query only pulls back info for the DBA privilege. any idea what might be wrong here?
SELECT XMLAgg(XMLElement("ROLE", A.GRANTED_ROLE,
(SELECT XMLAgg(XMLELement("PRIV", B.PRIVILEGE))FROM sys.dba_sys_privs B
FROM sys.dba_role_privs A JOIN sys.dba_sys_privs B
ON A.GRANTED_ROLE = B.GRANTEE
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!