Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

XML Generation in Oracle9i Using DBMS_XMLQUERY, DBMS_XMLGEN, SYS_XMLGEN and SYS_XMLAGG

Oracle9i has a number of XML generation mechanism with the most flexible being the XML SQL Utility (XSU). The XSU was introduced in Oracle 8i as a Java Servlet. In Oracle 9i its functionality has been extended, with a subset of it's functionality being incorporated into the database kernel giving improved performance along with Java and PL/SQL APIs. In this article I shall discuss DBMS_XMLQUERY PL/SQL interface to XSU, the DBMS_XMLGEN package and the SYS_XMLGEN and SYS_XMLAGG functions.

Related articles.

Basic XML Generation

This example uses the DBMS_XMLQUERY.GETXML function to return XML from a query. This is a courtesy function which performs all necessary actions but is rather inflexible.

SET SERVEROUTPUT ON
DECLARE
  v_file  UTL_FILE.file_type;
  v_xml   CLOB;
  v_more  BOOLEAN := TRUE;
BEGIN
  -- Create XML document from query.
  v_xml := DBMS_XMLQUERY.getxml('SELECT table_name, tablespace_name FROM user_tables WHERE rownum & 6');
  
  -- Output XML document to file.
  v_file := UTL_FILE.fopen('C:\Development\XML\', 'test1.xml', 'w');
  WHILE v_more LOOP
    UTL_FILE.put(v_file, Substr(v_xml, 1, 32767));
    IF LENGTH(v_xml) > 32767 THEN
      v_xml :=  SUBSTR(v_xml, 32768);
    ELSE
      v_more := FALSE;
    END IF;
  END LOOP;
  UTL_FILE.fclose(v_file);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(Substr(SQLERRM,1,255));
    UTL_FILE.fclose(v_file);
END;
/

Browser XSL Transformation

In this example the method used is more verbose and significatly more flexible. The case of all tags is changed and the default Rowset and Row tags are made more meaningful. In addition an Internet Explorer specific XSL stylesheet reference is added to allow the browser to transform the XML into HTML.

SET SERVEROUTPUT ON
DECLARE
  v_ctx   DBMS_XMLQUERY.ctxtype;
  v_file  UTL_FILE.file_type;
  v_xml   CLOB;
  v_more  BOOLEAN := TRUE;
BEGIN
  -- Create XML context.
  v_ctx := DBMS_XMLQUERY.newcontext('SELECT table_name, tablespace_name FROM user_tables WHERE rownum < 6');
  
  -- Set parameters to alter default Rowset and Row tag names and default case.
  DBMS_XMLQUERY.setrowsettag(v_ctx, 'user_tables'); 
  DBMS_XMLQUERY.setrowtag(v_ctx, 'table'); 
  DBMS_XMLQUERY.settagcase(v_ctx, DBMS_XMLQUERY.lower_case);
  
  -- Add an IE specfic XSL stylesheet reference so browser can transform the file.
  DBMS_XMLQUERY.setStylesheetHeader(v_ctx, 'C:\Development\XML\IEStyle.xsl', 'text/xsl');

  -- Create the XML document.
  v_xml := DBMS_XMLQUERY.getxml(v_ctx);
  DBMS_XMLQUERY.closecontext(v_ctx);
  
  -- Output XML document to file.
  v_file := UTL_FILE.fopen('C:\Development\XML\', 'test1.xml', 'w');
  WHILE v_more LOOP
    UTL_FILE.put(v_file, SUBSTR(v_xml, 1, 32767));
    IF LENGTH(v_xml) > 32767 THEN
      v_xml :=  SUBSTR(v_xml, 32768);
    ELSE
      v_more := FALSE;
    END IF;
  END LOOP;
  UTL_FILE.fclose(v_file);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(Substr(SQLERRM,1,255));
    UTL_FILE.fclose(v_file);
END;
/

Server XSL Transformation

This example is almost identicle to the last one, except that the DBMS_XMLQUERY.SETXSLT command tells the XSU to perform the XSL transformation during the XML generation. In this example the resulting file is actually HTML rather than XML. Since the XSU must read in the XLS, the JServer must have the appropriate filesystem access. This can be granted using the DBMS_JAVA.GRANT_PERMISSION procedure shown in the comments. In addition, the XSL used for this example can follow the W3C recommendation since IE will not be performing the transformation.

-- CONNECT sys/password@TSH AS SYSDBA
-- EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'java.io.FilePermission', '<<ALL FILES>>',  'read ,write, execute, delete');

SET SERVEROUTPUT ON
DECLARE
  v_ctx   DBMS_XMLQUERY.ctxtype;
  
  v_file  UTL_FILE.file_type;
  v_out   VARCHAR2(32767);
  v_xml   CLOB;
  v_more  BOOLEAN := TRUE;
BEGIN
  -- Create XML context.
  v_ctx := DBMS_XMLQUERY.newcontext('SELECT table_name, tablespace_name FROM user_tables WHERE rownum < 6');
  
  -- Set parameters to alter default Rowset and Row tag names and default case.
  DBMS_XMLQUERY.setrowsettag(v_ctx, 'user_tables'); 
  DBMS_XMLQUERY.setrowtag(v_ctx, 'table'); 
  DBMS_XMLQUERY.settagcase(v_ctx, DBMS_XMLQuery.LOWER_CASE);

  -- Add a W3C XSL stylesheet reference server can transform the XML file
  -- during the creation process. This way browsers without XML can still
  -- view file.
  DBMS_XMLQUERY.setxslt(v_ctx, 'C:\Development\XML\W3CStyle.xsl') ;

  -- Create the transformed HTML document.
  v_xml := DBMS_XMLQUERY.getxml(v_ctx);
  DBMS_XMLQUERY.closecontext(v_ctx);
  
  -- Output HTML document to file
  v_file := UTL_FILE.fopen('C:\Development\XML\', 'test1.html', 'w');
  WHILE v_more LOOP
    UTL_FILE.put(v_file, Substr(v_xml, 1, 32767));
    IF LENGTH(v_xml) > 32767 THEN
      v_xml :=  SUBSTR(v_xml, 32768);
    ELSE
      v_more := FALSE;
    END IF;
  END LOOP;
  UTL_FILE.fclose(v_file);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(Substr(SQLERRM,1,255));
    UTL_FILE.fclose(v_file);
END;
/

DBMS_XMLGEN

A subset of the XSU has been incorporated into the Oracle kernel to give better performance. The DBMS_XMLGen package is the API used to access this functionality. The example below is a copy of the previous code with all references to DBMS_XMLQUERY replaced by references to DBMS_XMLGEN. Note that two of the lines have been commented out as this functionality is currently not present in DBMS_XMLGEN. Where possible DBMS_XMLGEN should be used as it is more efficient than using the Java based DBMS_XMLQUERY.

SET SERVEROUTPUT ON
DECLARE
  v_ctx   DBMS_XMLGEN.ctxhandle;
  v_file  UTL_FILE.file_type;
  v_xml   CLOB;
  v_more  BOOLEAN := TRUE;
BEGIN
  -- Create XML context.
  v_ctx := DBMS_XMLGEN.newcontext('SELECT table_name, tablespace_name FROM user_tables WHERE rownum < 6');
  
  -- Set parameters to alter default Rowset and Row tag names and default case.
  DBMS_XMLGEN.setrowsettag(v_ctx, 'USER_TABLES'); 
  DBMS_XMLGEN.setrowtag(v_ctx, 'TABLE'); 
  --DBMS_XMLGEN.settagcase(v_ctx, DBMS_XMLGen.LOWER_CASE);
  
  -- Add an IE specfic XSL stylesheet reference so browser can transform the file.
  --DBMS_XMLGEN.setstylesheetheader(v_ctx, 'C:\Development\XML\IEStyle.xsl', 'text/xsl');

  -- Create the XML document.
  v_xml := DBMS_XMLGEN.getxml(v_ctx);
  DBMS_XMLGEN.closecontext(v_ctx);
  
  -- Output XML document to file.
  v_file := UTL_FILE.fopen('C:\Development\XML\', 'test1.xml', 'w');
  WHILE v_more LOOP
    UTL_FILE.put(v_file, SUBSTR(v_xml, 1, 32767));
    IF LENGTH(v_xml) > 32767 THEN
      v_xml :=  SUBSTR(v_xml, 32768);
    ELSE
      v_more := FALSE;
    END IF;
  END LOOP;
  UTL_FILE.fclose(v_file);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(Substr(SQLERRM,1,255));
    UTL_FILE.fclose(v_file);
END;
/

SYS_XMLGEN and SYS_XMLAGG

While the DBMS_XMLGEN package performs it's operations on the dataset as a whole, the SYS_XMLGEN function works on a per row basis, producing an XML document for each row of data. This data can then be aggregated into a single XML document using the Sys_XMLAgg function. The DBMS_XMLGEN function works on a single column or object. To create multi-column XML the appropriate database types must be defined.

CREATE OR REPLACE TYPE user_table_type AS OBJECT (
  table_name     VARCHAR2(30),
  tabspace_name  VARCHAR2(30)
);
/

DECLARE
  v_file  UTL_FILE.file_type;

  CURSOR c_xml IS
    SELECT SYS_XMLAGG(
             SYS_XMLGEN(
                user_table_type(table_name, tablespace_name), 
                sys.xmlgenformatType.createFormat('TABLE')
             ), 
             sys.xmlgenformatType.createFormat('USER_TABLES')
           ).getStringVal() AS xml_row
    FROM   user_tables
    WHERE  rownum < 6;
BEGIN

  v_file := UTL_FILE.fopen('C:\Development\XML\', 'test1.xml', 'w');
  FOR cure_rec IN c_xml LOOP
    UTL_FILE.put(v_file, cur_rec.xml_row);
  END LOOP;
  UTL_FILE.fclose(v_file);

END;
/

DROP TYPE user_table_type;

CAST And MULTISET

The CAST and MULTISET operators can be used with the appropriate object types to produce complex nested XML documents. Any of the previous examples can take advantage of this functionality.

CREATE OR REPLACE TYPE user_tab_column_type AS OBJECT (
  column_name    VARCHAR2(30)
);
/

CREATE OR REPLACE TYPE column_list_tab AS TABLE OF user_tab_column_type;
/

CREATE OR REPLACE TYPE user_table_type AS OBJECT (
  table_name     VARCHAR2(30),
  tabspace_name  VARCHAR2(30),
  column_names   column_list_tab
);
/

DECLARE
  v_file  UTL_FILE.file_type;

  CURSOR c_xml IS
    SELECT SYS_XMLAGG(
             SYS_XMLGEN(
               user_table_type(
                 ut.table_name,
                 ut.tablespace_name,
                 CAST(MULTISET(SELECT utc.column_name
                               FROM   user_tab_columns utc
                               WHERE  utc.table_name = ut.table_name)
                      AS column_list_tab
                     )
               ), 
               sys.xmlgenformatType.createFormat('TABLE')
             ), 
             sys.xmlgenformatType.createFormat('USER_TABLES')
           ).getStringVal() AS xml_row
    FROM   user_tables ut
    WHERE  rownum < 6;
BEGIN

  v_file := UTL_FILE.fopen('C:\Development\XML\', 'test1.xml', 'w');
  FOR cure_rec IN c_xml LOOP
    UTL_FILE.put(v_file, cur_rec.xml_row);
  END LOOP;
  UTL_FILE.fclose(v_file);

END;
/

DROP TYPE user_table_type;
DROP TYPE column_list_tab;
DROP TYPE user_tab_column_type;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.