DBMS_XMLGEN

The DBMS_XMLGEN package converts the results of a SQL query to a canonical XML format.

The package takes an arbitrary SQL query as input, converts it to XML format, and returns the result as a CLOB.

DBMS_XMLGEN.CTXHANDLE 

DBMS_XMLGEN.NEWCONTEXT
  Generates a new context handle from a query

DBMS_XMLGEN.CLOSECONTEXT
   This procedure closes a given context and releases all resources associated with it,

DBMS_XMLGEN.GETXML
  This function gets the XML document.
  Gets the XML document by fetching the maximum number of rows specified.
  It appends the XML document to the CLOB passed in

DBMS_XMLGEN.GETXMLTYPE

  Generates the XML document and returns it as a sys.XMLType

DBMS_XMLGEN.GETNUMROWSPROCESSED

   This function retrieves the number of SQL rows processed when generating the XML using the GETXML

DBMS_XMLGEN.RESTARTQUERY
  This procedure restarts the query and generates the XML from the first row.
  It can be used to start executing the query again, without having to create a new context.

DBMS_XMLGEN.SETCONVERTSPECIALCHARS
  This procedure sets whether or not special characters in the XML data must be converted into their escaped XML equivalent.
For example, the < sign is converted to &lt;.
The default is to perform conversions.
This function improves performance of XML processing when the input data cannot contain any special characters such as <, >, ",', which must be escaped.
It is expensive to scan the character data to replace the special characters,particularly if it involves a lot of data.

DBMS_XMLGEN.SETMAXROWS
  This procedure sets the maximum number of rows to fetch from the SQL query result for every invokation of the GETXML.It is used when generating paginated results.

DBMS_XMLGEN.SETNULLHANDLING
  This procedure sets NULL handling options, handled through the flag parameter setting.

  DROP_NULLS CONSTANT NUMBER:= 0; (Default) Leaves out the tag for NULL elements.
  NULL_ATTR CONSTANT NUMBER:= 1; Sets xsi:nil="true".
  EMPTY_TAG CONSTANT NUMBER:= 2; Sets, for example, <foo/>.

DBMS_XMLGEN.SETROWSETTAG
  This procedure sets the name of the root element of the document. The default name is ROWSET.

DBMS_XMLGEN.SETROWTAG
  This procedure sets the name of the element separating all the rows. The default name is ROW.

DBMS_XMLGEN.SETSKIPROWS
This procedure skips a given number of rows before generating the XML output for every call to the GETXML


Example:




declare
 sqlquery varchar2(200):= 'select ''kiran'' fname, ''kodavati'' lname from dual
                                       union all
                                       select ''vishali'' fname,''senivarapu'' lname from dual';

  xmlcontextHandle  dbms_xmlgen.CTXHANDLE;
  xmlclob_1 clob;
  numberofrowsfetched number;
  xmlclob_2 clob;
begin
  dbms_output.put_line(sqlquery);
  xmlcontextHandle:=DBMS_XMLGEN.NEWCONTEXT(sqlquery);
  xmlclob_1:=dbms_xmlgen.getXML(xmlcontextHandle);
  numberofrowsfetched:=dbms_xmlgen.getnumrowsprocessed(xmlcontextHandle);
  dbms_output.put_line(xmlclob_1);
  dbms_output.put_line('numberofrowsfetched:'||numberofrowsfetched);

  dbms_xmlgen.restartQuery(xmlcontextHandle);

  dbms_xmlgen.SETMAXROWS(xmlcontextHandle,1);
  xmlclob_2:=dbms_xmlgen.getXML(xmlcontextHandle);
  dbms_output.put_line(xmlclob_2);
  numberofrowsfetched:=dbms_xmlgen.getnumrowsprocessed(xmlcontextHandle);
  dbms_output.put_line('numberofrowsfetched:'||numberofrowsfetched);


  xmlclob_2:=dbms_xmlgen.getXML(xmlcontextHandle);
  dbms_output.put_line(xmlclob_2);

  numberofrowsfetched:=dbms_xmlgen.getnumrowsprocessed(xmlcontextHandle);
  dbms_output.put_line('numberofrowsfetched:'||numberofrowsfetched);

  dbms_xmlgen.restartQuery(xmlcontextHandle);
  dbms_xmlgen.SETMAXROWS(xmlcontextHandle,2);
  dbms_xmlgen.setRowSetTag(xmlcontextHandle,'DataSet');
  dbms_xmlgen.setRowTag(xmlcontextHandle,'DataRow');
  xmlclob_2:=dbms_xmlgen.getXML(xmlcontextHandle);
  dbms_output.put_line(xmlclob_2);

  dbms_xmlgen.closeContext(xmlcontextHandle);
end;