Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle dbms_xmlgen tips

Oracle Tips by Steve Karam, OCM, Oracle ACE


 

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

   

 


 

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational