Storing XML in the Database

Previously you could store XML in a CLOB data type. Then you could use DBMS_LOB to get at parts of the XML. This had the advantage that the original XML format was saved. However, the database did not know anything about the structure of the XML document in the CLOB.

The XMLTYPE was introduced in Oracle 9iR1. This allows the database to natively support XML. The type is treated as a user defined type with built in functions that operate on it. Therefore you can use it in SQL just like any other user defined type. The SQL can query parts of the XML document. You can use the type for column data types. You can use it in PL/SQL as types. It supports XML Schema.

There are different ways that XMLTYPE can internally store the data. However it is stored, an XML DOM tree structure can be constructed on demand when the need arises. Here are some options on how the XMLTYPE can internally store the data:

1. As a CLOB
2. Object Relational
3. Binary XML

CLOB format keeps the original XML document format. Performance is decreased. Object relational format will shred the XML. White spaces get removed. Performance is increased. The binary XML storage option was added in Oracle 11g. The XML gets parsed first then stored in binary form.

If you want to get a some parts of the XML, you can use the extract() function. Pass it an XPath string. It will give you back an XMLTYPE. Or you can use the extractvalue() function. It too takes an XPath string to specify the piece of XML you want. It however returns a scalar value.