Memory dump: Work with XMLField in Oracle (part 1)

Posted by in Software

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:

    You should get something like:

  • Check if the XDB user is there:

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

    and you’ll get back something like:

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


A little experiment: If you find this post and ad below useful, please check the ad out :-)