www.oradev.com
  Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
  How to use XML in the database
Parse a String in pl/sql
Oracle (var)char functions
Oracle connect by
Oracle numeric functions
Oracle date functions
Number format
Using a ref cursor
How to use the CASE statement
How to use the DECODE statement
How to use the NVL statement
How to use hints
Oracle date format
to_date function
Scheduling in 10g


  OraDev.com

Storing XML in the Oracle database.

Since Oracle 9iR1, you can store XML in the database as a seperate datatype: XMLType.
There are 2 ways to store data in XMLType:
1. Store XML in CLOB XMLType. (from Oracle 9iR1)
2. Store XML as structured data. Register an XML Schema and store XML in an XML-schema based XMLType using Object-relational storage.(from Oracle 9iR2)

Oracle also supplies a lot of standard functions for manipulating XML.

1. Store XML in CLOB XMLType

With the CLOB XMLType you can store XML documents as a Character Large OBject.
Using the CLOB XMLType storage there is no need for validation of the XML.
The XML does have to be well-formed, and it's optional to validate the XML document against an XML schema or DTD yourself.

With this type Oracle implicitly stores the XML in a CLOB column. The original XML is preserved including whitespace All restrictions on a CLOB column apply also to this XMLType.

Use CLOB storage for XMLType when:
- you are interested in storing and retrieving the whole document.
- you do not need to perform piece-wise updates on XML documents.

Code examples:
create table XmlTest( id number
                    , data_xml XMLType)
    XmlType data_xml STORE AS CLOB;
                    
                
insert into XmlTest(id, data_xml) values
( 1
, XMLType('<company>
             <department>
               <id>10</id>
               <name>Accounting</name>
             </department>
           </company>'));


create table XmlTest2 of XmlType;

INSERT INTO XmlTest2 VALUES (
  XMLType('<?xml version="1.0"?>
           <company>
               <department>
                 <id>10</id>
                 <name>Accounting</name>
               </department>
             </company>'
             ));
           

SELECT x.getCLobVal() from XmlTest2 x;

2. Register an XML Schema and store XML in an XML-schema based XMLType using Object-relational storage.

From version 9iR2 you can use XML-schema based XMLType.
This will give a much better performance.
XMLType combines the relational storage of the XML document with some standard W3C XML specific methods. The data is stored in the database using DOM. The Oracle database will know much more about the XML structure. You will have advantages like sorting, using XML constraints.

Code examples:
begin
 dbms_xmlschema.registerSchema(
 'https://www.oradev.com/sample.xsd',  
 '<schema xmlns="https://www.w3.org/2001/XMLSchema" 
  targetNamespace="https://www.oradev.com/sample.xsd" 
  xmlns:samp="https://www.oradev.com/sample.xsd"
  version="1.0">
  <simpleType name="departmentIdType">
   <restriction base="integer">
    <enumeration value="10"/>
    <enumeration value="20"/>
    <enumeration value="30"/>
    <enumeration value="40"/>
   </restriction>
  </simpleType>
  <element name="company">
  <complexType>
  <sequence>
   <element name="department">
    <complexType>
     <sequence>
      <element name = "id"   type = "samp:departmentIdType"/>
      <element name = "name" type = "string"/>
     </sequence>
    </complexType>
   </element>
  </sequence>
  </complexType>
  </element>
</schema>',
 TRUE, TRUE, FALSE, FALSE);
end;
Now you can create an XMLSchema-based table, as shown in the following example:
CREATE TABLE XmlTest OF XMLType
   XMLSCHEMA "https://www.oradev.com/sample.xsd"
   ELEMENT "company";
Now XML can be inserted into the XmlTest table as following:
 INSERT INTO XmlTest VALUES(
 xmltype.createxml('<?xml version="1.0"?>
   <samp:company xmlns:samp="https://www.oradev.com/sample.xsd" >
      <department>
         <id>10</id>
         <name>Accounting</name>
      </department>
    </samp:company>'));
    
    
CREATE TABLE XmlTest2 (
  id number
, xml_data XmlType)
   XMLTYPE xml_data STORE AS OBJECT RELATIONAL
      XMLSCHEMA "https://www.oradev.com/sample.xsd"
      ELEMENT "company";

    
     INSERT INTO XmlTest2 VALUES(1,
 xmltype.createxml('<?xml version="1.0"?>
   <samp:company xmlns:samp="https://www.oradev.com/sample.xsd" >
      <department>
         <id>10</id>
         <name>Accounting</name>
      </department>
    </samp:company>'));
It's also possible to create constraints on on the XMLSchema-based table.