8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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.
- Basic XML Generation
- Browser XSL Transformation
- Server XSL Transformation
- DBMS_XMLGEN
- SYS_XMLGEN and SYS_XMLAGG
- CAST and MULTISET
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 cur_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 cur_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...