TL;DR: Here I present an introduction of the problem and some prerequisites to check if you have XMLFIELD available.

Table of Contents

  1. Part 1 - Prerequisites
  2. Part 2 - Create a table and perform a SELECT
  3. Part 3 - User Python to insert data
  4. Part 4 - references

Introduction

Recently, I've got a request to import a bunch of XML files in a database (Oracle). After reading a lot on mapping XML on a relational DB (and considering NoSQL databases too), I was considering using a script to generate the DB schema and hopefully load the data. To figure out this, I've used the interesting package GenerateDS for python, and specifically its django generation feature. I won't bore you with the details, but the conclusion is that I ended up with 160+ tables. Crazy!

Simultaneously, I found out that Oracle offers the possibility to store XML documents in tables via its XMLTYPE data field. Not only is it able to store it, but you can even perform SQL queries (e.g. SELECT) on it. Cool, or what? What's even better, is that it can become quite efficient if the XML is attached a schema (one guy on stackoverflow experienced a reduction from several hours to less than one second!).

Prerequisites

First, this is an Oracle-specific approach. If you don't have Oracle DB, the use is somewhat limited.

Now that we cleared things up a bit, here are the steps you need to perform to figure out if you can access the type or not:

  • First, you may query the Oracle version via:

    select * from v$version;

    You should get something like:

    Oracle Database 11g [...] 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE   11.2.0.3.0  Production"
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

  • Check if the XDB user is there:

    SELECT \* FROM ALL_USERS;

  • Last thing is to check if you have XMLTYPE available. You need to run:

    DESCRIBE RESOURCE_VIEW;

    and you'll get back something like:

    DESCRIBE RESOURCE_VIEW
Name     Null Type
-------- ---- --------------
RES           XMLTYPE()
ANY_PATH      VARCHAR2(4000)
RESID         RAW(16 BYTE)

So... if you have XDB and XMLTYPE(), then you're good to go.