Hi all,
Using evalname, one can create a XML with dynamic elements.
Test case:
create table manel_allowed_permissions(permission VARCHAR2(10));
create table manel_menu_permissions(menu_id number,permission VARCHAR2(10));
insert into manel_allowed_permissions VALUES ('CREATE');
insert into manel_allowed_permissions VALUES ('READ');
insert into manel_allowed_permissions VALUES ('UPDATE');
insert into manel_allowed_permissions VALUES ('DELETE');
insert into manel_menu_permissions VALUES (1,'CREATE');
insert into manel_menu_permissions VALUES (1,'READ');
insert into manel_menu_permissions VALUES (1,'UPDATE');
--
COMMIT;
SELECT xmlserialize(content xmlelement("PERMISSIONS",
xmlagg(xmlelement(evalname(ap.permission),
CASE
WHEN mp.permission IS NOT NULL THEN
'TRUE'
ELSE
'FALSE'
END))) AS BLOB encoding 'UTF-8') perm_xml
FROM manel_allowed_permissions ap,
manel_menu_permissions mp
WHERE ap.permission = mp.permission(+)
AND mp.menu_id(+) = 1;
The Output is:
<?xml version="1.0"?>
<PERMISSIONS>
<CREATE>TRUE</CREATE>
<READ>TRUE</READ>
<UPDATE>TRUE</UPDATE>
<DELETE>FALSE</DELETE>
</PERMISSIONS>
If I for example add a new allowed permission type, the Output is dynamically changed:
insert into manel_allowed_permissions VALUES ('EXPORT');
COMMIT;
The output of the query is:
<?xml version="1.0"?>
<PERMISSIONS>
<CREATE>TRUE</CREATE>
<READ>TRUE</READ>
<UPDATE>TRUE</UPDATE>
<DELETE>FALSE</DELETE>
<EXPORT>FALSE</EXPORT>
</PERMISSIONS>
My question is: Is there a way of using XMLTABLE in order to go from the Output XML to rows knowing that the permissions can change?
I was only able to do this using UNPIVOT, but this way I always have to prior know all available permissions.
Thanks in advance.