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. |
 |
This package converts the result of an SQL statement passed as inputs to its functions, into an XML document.
The GETXMLTYPE function converts the result of the input statement into an XMLTYPE data as shown below,
SELECT dbms_xmlgen.getxmltype('select first_name, last_name, phone_number, email from employees where employee_id in (100,101)')
FROM dual;
Result:
<?xml version="1.0"?>
<ROWSET>
<ROW>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>King</LAST_NAME>
<PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
<EMAIL>SKING</EMAIL>
</ROW>
<ROW>
<FIRST_NAME>Neena</FIRST_NAME>
<LAST_NAME>Kochhar</LAST_NAME>
<PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
<EMAIL>NKOCHHAR</EMAIL>
</ROW>
</ROWSET>
The GETXML function converts the result of the input statement into an XML document of CLOB type as shown below,
SELECT dbms_xmlgen.getxml('select first_name, last_name, phone_number, email from employees where employee_id in (100,101)')
FROM dual;
Result:
<ROWSET>
<ROW>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>King</LAST_NAME>
<PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
<EMAIL>SKING</EMAIL>
</ROW>
<ROW>
<FIRST_NAME>Neena</FIRST_NAME>
<LAST_NAME>Kochhar</LAST_NAME>
<PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
<EMAIL>NKOCHHAR</EMAIL>
</ROW>
</ROWSET>
How does one transform data in a table to XML
format? Just as one can select and format into
HTML code, and this is what takes place behind
the scene in iSQL*Plus, one can select from a
table and have the output be well-formed XML.
Use the DBMS_XMLGEN.GETXML
function to accomplish this.
The syntax is select DBMS_XMLGEN.GETXML(?your
query here?) from dual and with
spool and SQL*Plus settings set correctly, the
output is a dump of data in XML format. Where is
this useful?
Anywhere or anytime one needs to transform data
into XML format, the GETXML function can be
used. Of particular note, Oracle's new reporting
tool Business Intelligence Publisher is
intimately tied to XML. In fact, report or
template development is largely driven by having
an XML file representation of data to start
with. Dump a portion of the data into XML
format, load the XML data into an RTF document
in Word, call the table wizard, and one has a
report template just like that.
Of course, much more can take place with respect
to manipulating the data. Oracle recommends that
data selection and formatting, as much as
possible, be done via the SELECT statement as
opposed to forcing the RTF processing engine to
manipulate the data. The RDBMS engine is
obviously much more powerful than what Microsoft
Word has to offer.
A select statement like so?
SQL> select
dbms_xmlgen.getxml(
2
'select
3
EMPNO,
4
ENAME,
5
JOB,
6
MGR,
7
HIREDATE,
8
SAL,
9
COMM,
10
DEPTNO
11
from emp
12
where deptno=10')
13
"XML OUTPUT"
14
from dual;
?yields the following output:
XML OUTPUT
---------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-JUN-81</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
?continued?
</ROWSET>
The ROWSET and ROW tags can be
set via other procedures within this package.
Oracle
recommends that DBMS_XMLGEN be used over
DBMS_XMLQUERY.
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
and include the URL for the page.