Oracle: Generate a XML document with your SQL Query

Oracle provides excellent built in packages to handle XML documents in much easier way than before.
You could generate a simple XML documents by passing your SQL query. The output of the query can be simply converted to a XML document without much overhead.

Following function will return a CLOB data (which is your output XML document) by passing a SQL query. The Result of the query is simply converted to a XML document.

FUNCTION generate_xml
(
   par_query CLOB,
   par_record VARCHAR2 DEFAULT 'RECORD',
   par_root VARCHAR2 DEFAULT 'ROOT'
) RETURN CLOB
IS
  qryCtx DBMS_XMLGEN.ctxHandle;
  result CLOB;
BEGIN
  IF NOT par_query IS NULL THEN
  qryCtx := dbms_xmlgen.newContext(par_query);
  -- set the ROOT element Name
  DBMS_XMLGEN.setrowsettag(qryCtx, par_root);
  -- set the row header Name
  DBMS_XMLGEN.setRowTag(qryCtx,par_record);
  -- now get the result
  result := DBMS_XMLGEN.getXML(qryCtx);
  --close context
  DBMS_XMLGEN.closeContext(qryCtx);
  RETURN result;
ELSE
   RETURN NULL;
END IF;
EXCEPTION
  WHEN others THEN
   DBMS_XMLGEN.closeContext(qryCtx);
    RETURN NULL;
END generate_xml ;


Example : SELECT GENERATE_XML ('SELECT * FROM user_objects WHERE ROWNUM <=100') result FROM DUAL;