Search from 700 + Posts

Aug 24, 2011

XML Processing in Oracle XMLDB(database)

Oracle XML DB –

This blog would explains in details about XML DB.I will explain what are the feature and advantages of using Oracle XML DB.

Oracle XML database is a high performance XML storage and retrieval technology.It is a feature of Oracle Database that provides a powerful tool for managing XML content, including storage, manipulation, and retrieval. It offers different storage options to meet the unique requirements of different
XML formats. These options include unstructured, binary, and structured storage:

Unstructured (character large object, or CLOB). By treating the document as one large object and storing it in the database, this method allows for the best insertion times. Unstructured storage can be a
 practical solution if disk space is not an issue and the objective is to archive the documents in their original format.

Binary storage. This option, new in Oracle Database 11g, stores data in a post parse binary format designed specifically for XML data. This option has several advantages over unstructured storage, in that it is XML-schema-aware, allowing better disk space efficiency and query performance.

Structured storage (Table & Columns). Also known as schema-based storage, this option uses an object-relational model to store XML documents in the database.

With XML DB we can store structure data in table & columns and unstructured data into LOB. It enables us to perform
  • XML Operations on Structure data
  • SQL operation son XML documents.

Oracle XML DB introduced a new data type name XMLType, It combines SQL and XML features. It allows storing the XML data into CLOB as well as Structure Storage (table & Column). It can be used as Data type for a column, and object table. It provides a SQL API to operate on XML data.

CLOB option is unstructured and thus retains the original document. But on the other hand Structure storage will store the XML data in decomposition XML form in table & column for better SQL query and performance.
Advantage of the XMLType are , we can
   1. Perform SQL queries on XML data.
   2. Perform XPath searches and XSL transformations.

XMLType is a system-defined opaque type for handling XML data. XMLType has predefined member functions on it to extract XML nodes and fragments.

You can create columns of XMLType and insert XML documents into it. You can also generate XML documents as XMLType instances dynamically using the SYS_XMLGEN and SYS_XMLAGG SQL functions.

From 9i onwards below is structure of the database from XML prospect.

Oracle offers lot of SQL built in functions to generate XML.In this blog I will explain how to use these functions with example.
You can generate XML data using any of the following standard SQL/XML functions supported by Oracle XML DB:

Below I will explain some of the Common command and their synatx provided by oracle to generate the XML. Please note that I have covered only few of the syntax provided by oracle. For please complete list please refer Oracle XMLDB developer Guide.

In My examples ,
I have pasted the Synatx Diagram of the command from Orcle developer guide and then
and then uses the same command to generate the XML from oracle Application Order Management database.


--XML Element
select line_id,
    XMLELEMENT("LINEID", line_id) ,
    XMLELEMENT("Item", ordered_item)
from oe_order_lines_all
where creation_date between sysdate-17 and sysdate

--XML Attribute

select line_id,
  XMLATTRIBUTES(header_id, open_flag),
    XMLFOREST(line_id,ordered_item, ordered_quantity, NVL(shipped_quantity,0) Shipped_quantity,
              NVL(fulfilled_quantity,0) Fulfilled_quantity,
              NVL(invoiced_quantity,0) Invoiced_quantity )
from oe_order_lines_all
where creation_date between sysdate-17 and sysdate

--XML Forest

select line_id,
  XMLFOREST(line_id,ordered_item, ordered_quantity, NVL(shipped_quantity,0) Shipped_quantity,
            NVL(fulfilled_quantity,0) Fulfilled_quantity,
            NVL(invoiced_quantity,0) Invoiced_quantity )
from oe_order_lines_all
where creation_date between sysdate-17 and sysdate

--XML Sequence

select l.*
    (XMLSEQUENCE(cursor (select ordered_item, ordered_quantity
                          from oe_order_lines_all
                          where ordered_quantity > 1)
    ) l

--XMLAGG() function can be used to concat XMLTYPE instance across the rows.
--It  will print everything on one lilne
-- If we add group by CLAUSE then new group start with New line.

      XMLFOREST(header_id, ordered_item, ordered_quantity, line_id, creation_date))
      order by creation_date))--,ordered_item, ordered_quantity, line_id, creation_date))
from oe_order_lines_all l
where creation_date between sysdate-17 and sysdate
group by header_id

--XML Concat

select line_id,
 XMLATTRIBUTES(l.header_id, l.open_flag),
    XMLELEMENT("LINEID"           ,l.line_id),
    XMLELEMENT("ITEM"             ,l.ordered_item),
    XMLELEMENT("OrderedQuantity"  , l.ordered_quantity)
from oe_order_lines_all l
where creation_date between sysdate-17 and sysdate 

This Oracle specific SQL function is similar to the XMLElement() except that it takes a single argument and converts the result to XML. Unlike the other XML generation functions, SYS_XMLGEN() always returns a well-formed XML document. Unlike DBMS_XMLGEN which operates at a query level, SYS_XMLGEN() operates at  the row level returning a XML document for each row.

SYS_XMLGEN() is powerful for the following reasons:
You can create and query XML instances within SQL queries.
Using the object-relational infrastructure, you can create complex and nested XML instances
from simple relational tables.

select SYS_XMLGEN(line_id)
from oe_order_lines_all
where creation_date between sysdate-17 and sysdate

select SYS_XMLGEN(line_id||header_id)
from oe_order_lines_all
where creation_date between sysdate-17 and sysdate


The DBMS_XMLGEN package converts the results of a SQL query to a canonical XML format. The package takes an arbitrary SQL query as input, converts it to XML format, and returns the result as a CLOB. This package is similar to the DBMS_XMLQUERY package, except that it is written in C and compiled into the kernel. This package can only be run on the database.

The difference between SYS_XMLGEN() function and DBMS_XMLGEN package is apparent from the preceding example:
SYS_XMLGEN works inside SQL queries and operates on the expressions and columns within the rows DBMS_XMLGEN works on the entire result set

xmlctx       dbms_xmlgen.ctxHandle;--ctxType;
l_count   NUMBER;  
a clob;
xmlctx:= dbms_xmlgen.newcontext('select line_id from oe_order_lines_all where creation_date between sysdate-17 and sysdate');
a := dbms_xmlgen.getxml(xmlctx);
l_count :=DBMS_XMLGEN.getnumrowsprocessed(xmlctx);
dbms_output.put_line('No. of rows processed are  '|| l_count);

As I have explained XMLTYPE
1.    Is a datatype for database columns, object type attributes, object tables and views.
2.    Can be queried with SQL , using built functions.
3.    Can ve used in PL/SQL , Parameters , function return.
XMLTYPE use below to function to query about XML
·    Extract()
·    ExistsNode()

In below examples I will explain in details .

create table my_resume_clob
employee_id     NUMBER,
employee_name   VARCHAR2(200),
resume          XMLTYPE)

Insert data in table

select * from my_resume_clob
where existsNode(resume,'/RESUME[Location ="CA"]') =1

select existsNode(resume,'//Location[text() ="CA"]'),e.resume.extract('//Location/text()'),  e.* from my_resume_clob e
where existsNode(resume,'//Location[text() ="CA"]')=1

select e.resume.extract('//Location/text()'), extract(e.resume, '//Location/text()'),extractvalue(resume,'//Location')
from my_resume_clob e 
where extractvalue(resume,'//Location')='CA'


  1. Hi,
    In XML db we know to mass load images using FTP.

    We need a way to load individual image file from a PC to XML db.
    Does anyone know of any utility for this please.

    We have a input text box with a "Browse" button to select an image stored locally on the userb^Ys computer. The browse button will open the
    file selector window. Once a file is selected, the path to that file is returned back to the input box. The upload button will upload the file
    to the XML DB.

  2. This is really great blog , your post always save me lot of time.