Memory dump: Work with XMLField in Oracle (part 1)
TL;DR: Here I present an introduction of the problem and some prerequisites to check if you have XMLFIELD available.
Table of Contents
- Part 1 – Prerequisites
- Part 2 – Create a table and perform a SELECT
- Part 3 – User Python to insert data
- Part 4 – references
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!).
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:
1select * from v$version;
You should get something like:12345Oracle Database 11g [...] 126.96.36.199.0 - 64bit ProductionPL/SQL Release 188.8.131.52.0 - Production"CORE 184.108.40.206.0 Production"TNS for Solaris: Version 220.127.116.11.0 - ProductionNLSRTL Version 18.104.22.168.0 - Production
- Check if the XDB user is there:
1SELECT * FROM ALL_USERS;
- Last thing is to check if you have XMLTYPE available. You need to run:
and you’ll get back something like:123456DESCRIBE RESOURCE_VIEWName 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.
A little experiment: If you find this post and ad below useful, please check the ad out :-)