Oracle XML support
| I'll be happy to come on-site and help you with any of your Oracle XML issues, and I also offer a very popular class on Oracle XML development. Just call to schedule my on-site visit. |
 |
Tips for using dbms_xmlgen
Wouldn’t it be fantastic if you could simply pull data from Oracle preformatted with XML tags? Many Oracle shops use XML for data transfer, web services, reports, and you can easily tag your Oracle data using the dbms_xmlgen package. Oracle’s XML Publisher product can retrieve XML from an HTTP feed and use it to generate rich reports with graphs, images, and other content, and then mail, fax, print, or FTP them. All we need is the XML.
Oracle gives us the dbms_xmlgen package for formatting Oracle output in XML. The dbms_xmlgen package generates XML on-the-fly using any query you want; in addition, it’s extremely easy to use from either the SQL prompt or in code, as it’s just a simple query.
Generating XML from Oracle with dbms_xmlgen
Take this standard query:
SQL> select employee_id, first_name, last_name, phone_number
2 from employees where rownum < 6
EMPLOYEE_ID FIRST_NAME LAST_NAME PHONE_NUMBER
----------- -------------------- ------------------------- --------------------
100 Steven King 515.123.4567
101 Neena Kochhar 515.123.4568
102 Lex De Haan 515.123.4569
103 Alexander Hunold 590.423.4567
104 Bruce Ernst 590.423.4568
We get our standard output with no frills. Behold as we transform this into XML, by simply adding the dbms_xmlgen.getxml procedure to the SQL select statement!
set pages 0
set linesize 150
set long 9999999
set head off
SQL> select dbms_xmlgen.getxml('select employee_id, first_name,
2 last_name, phone_number from employees where rownum < 6') xml
3 from dual
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPLOYEE_ID>100</EMPLOYEE_ID>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>King</LAST_NAME>
<PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
</ROW>
<ROW>
<EMPLOYEE_ID>101</EMPLOYEE_ID>
<FIRST_NAME>Neena</FIRST_NAME>
<LAST_NAME>Kochhar</LAST_NAME>
<PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
</ROW>
<ROW>
<EMPLOYEE_ID>102</EMPLOYEE_ID>
<FIRST_NAME>Lex</FIRST_NAME>
<LAST_NAME>De Haan</LAST_NAME>
<PHONE_NUMBER>515.123.4569</PHONE_NUMBER>
</ROW>
<ROW>
<EMPLOYEE_ID>103</EMPLOYEE_ID>
<FIRST_NAME>Alexander</FIRST_NAME>
<LAST_NAME>Hunold</LAST_NAME>
<PHONE_NUMBER>590.423.4567</PHONE_NUMBER>
</ROW>
<ROW>
<EMPLOYEE_ID>104</EMPLOYEE_ID>
<FIRST_NAME>Bruce</FIRST_NAME>
<LAST_NAME>Ernst</LAST_NAME>
<PHONE_NUMBER>590.423.4568</PHONE_NUMBER>
</ROW>
</ROWSET>
Beautiful! We get fully compliant XML that can be easily integrated into any application, with ROWSET and ROW tags in-place to identify nodes, and XML tags for each column from the SQL query using the dbms_xmlgen.getxml procedure.
All we had to do was wrap the query in the dbms_xmlgen.getxml function call, selected from DUAL. Otherwise, the SQL query syntax remained exactly the same.
Advanced Uses for dbms_xmlgen
Let’s take a look at more advanced XML tagging with dbms_xmlgen. Most XML has sub-nodes for each main node. For instance, what if we wanted to pull XML for every department, and a sub-node for every employee under it? We can use the CURSOR function!
SQL> select department_id, department_name,
2 cursor(select first_name, last_name
3 from employees e
4 where e.department_id = d.department_id) emp_row
5 from departments d
6* where rownum < 4
DEPARTMENT_ID DEPARTMENT_NAME EMP_ROW
------------- ------------------------------ --------------------
10 Administration CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
FIRST_NAME LAST_NAME
-------------------- -------------------------
Jennifer Whalen
20 Marketing CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
FIRST_NAME LAST_NAME
-------------------- -------------------------
Michael Hartstein
Pat Fay
30 Purchasing CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
FIRST_NAME LAST_NAME
-------------------- -------------------------
Den Raphaely
Alexander Khoo
Shelli Baida
Sigal Tobias
Guy Himuro
Karen Colmenares
The results don’t look too impressive at the SQL prompt. However, watch as we surround it with a call to dbms_xmlgen.getxml:
SQL> select dbms_xmlgen.getxml('
2 select department_id, department_name,
3 cursor(select first_name, last_name
4 from employees e
5 where e.department_id = d.department_id) emp_row
6 from departments d
7 where rownum < 4
8* ') from dual
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPARTMENT_ID>10</DEPARTMENT_ID>
<DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
<EMP_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Jennifer</FIRST_NAME>
<LAST_NAME>Whalen</LAST_NAME>
</EMP_ROW_ROW>
</EMP_ROW>
</ROW>
<ROW>
<DEPARTMENT_ID>20</DEPARTMENT_ID>
<DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
<EMP_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Michael</FIRST_NAME>
<LAST_NAME>Hartstein</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Pat</FIRST_NAME>
<LAST_NAME>Fay</LAST_NAME>
</EMP_ROW_ROW>
</EMP_ROW>
</ROW>
<ROW>
<DEPARTMENT_ID>30</DEPARTMENT_ID>
<DEPARTMENT_NAME>Purchasing</DEPARTMENT_NAME>
<EMP_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Den</FIRST_NAME>
<LAST_NAME>Raphaely</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Alexander</FIRST_NAME>
<LAST_NAME>Khoo</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Shelli</FIRST_NAME>
<LAST_NAME>Baida</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Sigal</FIRST_NAME>
<LAST_NAME>Tobias</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Guy</FIRST_NAME>
<LAST_NAME>Himuro</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Karen</FIRST_NAME>
<LAST_NAME>Colmenares</LAST_NAME>
</EMP_ROW_ROW>
</EMP_ROW>
</ROW>
</ROWSET>
Note that I didn’t change the query syntax in any way. But check out the great XML results! We have each DEPARTMENT as a ROW tag, and the cursor we created gives us an EMP_ROW node containing recurring EMP_ROW_ROW nodes.
Tips for using dbms_xmlgen
The dbms_xmlgen package can be extremely useful for quick retrieval of web records. Simply make a page that accepts input, such as DEPARTMENT_ID, and passes it into your SQL query. If you direct the SQL*Plus response directly to the screen, and you have an easy XML display program.
The best part of dbms_xmlgen is for quickly formatting reports. XML Publisher will accept standard XML and allow you to form extremely detailed reports using templates made in Microsoft Word.
With standard SQL queries tagged using dbms_xmlgen, XML Publisher you can have a full reporting suite that easily pulls Oracle data with XML tags, forms it into a PDF, DOC, XLS, or HTML report, and distributes your report via e-mail using its native e-mail capabilities. This is far easier than the traditional utl_mail or utl_smtp e-mail packages which required specialized invocation code.
Oracle 11g provides the following information about the DBMS_XMLGEN package:
Description of the DBMS_XMLGEN package:
PROCEDURE DBMS_XMLGEN.CLEARBINDVALUES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
PROCEDURE DBMS_XMLGEN.CLOSECONTEXT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
FUNCTION DBMS_XMLGEN.CONVERT RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
XMLDATA VARCHAR2 IN
FLAG NUMBER IN DEFAULT
FUNCTION DBMS_XMLGEN.CONVERT RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
XMLDATA CLOB IN
FLAG NUMBER IN DEFAULT
FUNCTION DBMS_XMLGEN.GETNUMROWSPROCESSED RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
PROCEDURE DBMS_XMLGEN.GETXML
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
TMPCLOB CLOB IN/OUT
DTDORSCHEMA NUMBER IN DEFAULT
FUNCTION DBMS_XMLGEN.GETXML RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
DTDORSCHEMA NUMBER IN DEFAULT
FUNCTION DBMS_XMLGEN.GETXML RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQLQUERY VARCHAR2 IN
DTDORSCHEMA NUMBER IN DEFAULT
PROCEDURE DBMS_XMLGEN.GETXMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
TMPXMLTYPE XMLTYPE IN/OUT
DTDORSCHEMA NUMBER IN DEFAULT
FUNCTION DBMS_XMLGEN.GETXMLTYPE RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
DTDORSCHEMA NUMBER IN DEFAULT
FUNCTION DBMS_XMLGEN.GETXMLTYPE RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQLQUERY VARCHAR2 IN
DTDORSCHEMA NUMBER IN DEFAULT
FUNCTION DBMS_XMLGEN.NEWCONTEXT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUERYSTRING VARCHAR2 IN
FUNCTION DBMS_XMLGEN.NEWCONTEXT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUERYSTRING REF CURSOR IN
FUNCTION DBMS_XMLGEN.NEWCONTEXTFROMHIERARCHY RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUERYSTRING VARCHAR2 IN
PROCEDURE DBMS_XMLGEN.REMOVEXSLTPARAM
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
NAME VARCHAR2 IN
PROCEDURE DBMS_XMLGEN.RESTARTQUERY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
PROCEDURE DBMS_XMLGEN.SETBINDVALUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
BINDNAME VARCHAR2 IN
BINDVALUE VARCHAR2 IN
PROCEDURE DBMS_XMLGEN.SETCHECKINVALIDCHARS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
CHK BOOLEAN IN
PROCEDURE DBMS_XMLGEN.SETCONVERTSPECIALCHARS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
REPLACE BOOLEAN IN
PROCEDURE DBMS_XMLGEN.SETINDENTATIONWIDTH
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
WIDTH NUMBER IN
PROCEDURE DBMS_XMLGEN.SETMAXROWS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
MAXROWS NUMBER IN
PROCEDURE DBMS_XMLGEN.SETNULLHANDLING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
FLAG NUMBER IN
PROCEDURE DBMS_XMLGEN.SETPRETTYPRINTING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
PP BOOLEAN IN
PROCEDURE DBMS_XMLGEN.SETROWSETTAG
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
ROWSETTAGNAME VARCHAR2 IN
PROCEDURE DBMS_XMLGEN.SETROWTAG
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
ROWTAGNAME VARCHAR2 IN
PROCEDURE DBMS_XMLGEN.SETSKIPROWS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
SKIPROWS NUMBER IN
PROCEDURE DBMS_XMLGEN.SETXSLT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
STYLESHEET CLOB IN
PROCEDURE DBMS_XMLGEN.SETXSLT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
STYLESHEET XMLTYPE IN
PROCEDURE DBMS_XMLGEN.SETXSLT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
URI VARCHAR2 IN
PROCEDURE DBMS_XMLGEN.SETXSLTPARAM
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
NAME VARCHAR2 IN
VALUE VARCHAR2 IN
PROCEDURE DBMS_XMLGEN.USEITEMTAGSFORCOLL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
PROCEDURE DBMS_XMLGEN.USENULLATTRIBUTEINDICATOR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
ATTRIND BOOLEAN IN DEFAULT
