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.
|