Using dbms_xmlgen

Oracle's DBMS_XMLGEN is a cool package to generate xml data for a simple query(single-node) or complex query(with sub-nodes)


e.g.1:
SQL>select user_firstname, user_lastname
from arm$_user
where rownum <= 11;

With DBMS_XMLGEN
SQL>select dbms_xmlgen.getxml('
select user_firstname, user_lastname
from arm$_user
where rownum <= 11') user_node
from dual;

e.g.2:
SQL>select product_id, product_name, product_createdate from product where rownum <>select department_id, department_name,
cursor(select user_firstname, user_lastname
from arm$_user u
where u.department_id = d.department_id) userrow
from arm$_department d
where rownum <= 11;

With DBMS_XMLGEN with sub-nodes
SQL>select dbms_xmlgen.getxml('
select department_id, department_name,
cursor(select user_firstname, user_lastname
from arm$_user u
where u.department_id = d.department_id) userrow
from arm$_department d
where rownum <= 11') with_snode
from dual;