HOW TO GENERATE XML DATA FROM PLSQL USING DBMS_XMLGEN

 

By using DBMS_XMLDOM we can generate xml output.

DECLARE

l_xmltype XMLTYPE;

l_ctx dbms_xmlgen.ctxhandle;

BEGIN

l_ctx := dbms_xmlgen.newcontext

('SELECT DNAME,LOC

FROM depT

WHERE depTNO in (10,20)'

);
dbms_xmlgen.setrowsettag(l_ctx, 'Departments');

dbms_xmlgen.setrowtag(l_ctx, 'DNAME');

l_xmltype := dbms_xmlgen.getXmlType(l_ctx) ;

dbms_xmlgen.closeContext(l_ctx);

dbms_output.put_line(l_xmltype.getClobVal);

End;

==========================================

OUTPUT WILL BE

 

<Departments>
<DNAME>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</DNAME>
<DNAME>
<DNAME>RESEARCH</DNAME>
<LOC>DALLAS</LOC>
</DNAME>
</Departments>