How to generate XML using SQL query in Oracle
1. Selecting single column from a table
SELECT SYS_XMLAGG(SYS_XMLGEN(franchisee_codes))
FROM franchisee_mapping
WHERE username = '<username>';
The output will be
<?xml version="1.0"?>
<ROWSET>
<FRANCHISEE_CODES>WMHPUNBHAGWATIELEC_D</FRANCHISEE_CODES>
<FRANCHISEE_CODES>WMHPUNBHAGWATIELEC</FRANCHISEE_CODES>
</ROWSET>
2. Selecting multiple columns from a table
select
DBMS_XMLGEN.getXML('select franchisee_codes,role
FROM franchisee_mapping
WHERE username = ''<username>'' and rownum<3' ) from dual;
The output will be
<?xml version="1.0"?>
<ROWSET>
<ROW>
<FRANCHISEE_CODES>WMHPUNBHAGWATIELEC_D</FRANCHISEE_CODES>
<ROLE>WORK</ROLE>
</ROW>
<ROW>
<FRANCHISEE_CODES>WMHPUNBHAGWATIELEC</FRANCHISEE_CODES>
<ROLE>WORK</ROLE>
</ROW>
</ROWSET>