Coding Insomnia

So, in my last post, I told you about the Oracle XMLType and how to add XML data to tables that are based on XML Schemas. Now, storing data is not really that useful if you can’t work with it, and this is where the XMLType really starts to get interesting.

I think it’s time we get our hands dirty with some practical examples.. Have you ever tried to make a nice hierarchical forum by using a relational database? You’ll probably implement it with a “recursive” table, which is really nice from a logical viewpoint, something like this:

ForumThread
ThreadID – Integer (PK)
Subject – String
RootEntryID – Integer (FK to ForumEntry)

ForumEntry
EntryID – Integer
Content – String
UserID – Integer (FK to some User table)
ParentEntryID – Integer (FK to ForumEntry)

However, when you try to actually use it it quickly becomes more difficult. You end up with creating recursive loops that require loads of database lookups just to display the hierarchy. When I did this the last time, I actually gave up and decided that a one-level hierarchy would have to be enough.

But hey, isn’t XML pretty good at representing hierarchy? Let’s try to do the same thing, but instead of having a ParentEntryID column on each Entry, we keep all this information in an XMLType column on the Thread!

ForumThread
ThreadID – Integer (PK)
Subject – String
Hierarchy – XMLType

ForumEntry
EntryID – Integer (PK)
UserID – Integer (FK to some User table)
Content – String

The XML stored in “Hierarchy” could look something like:

<Thread>
 <Entry ID="1">
  <Entry ID="2" />
  <Entry ID="3" />
  <Entry ID="4">
   <Entry ID="5" />
   <Entry ID="6" />
  </Entry>
  <Entry ID="7" />
 </Entry>
</Thread>

Note that this is rather simplified.. you probably want to add some more fields etc..

Now, last time I promised to show you a little bit on how to query the stored XML using XPath expressions.. You do this by using one of two basic methods; “existsNode()” and “extractValue()”.

Let’s say, for example, that you want to know which ThreadID that contains the Entry with an ID of 4. You do this with a query like this:

SELECT t.ThreadID FROM THREAD t WHERE existsNode(t.Hierarchy, '//Entry[@ID=4]')

Or, let’s say that you want to know the EntryID of the root entry for all threads:

SELECT extractValue(t.Hierarchy, '/Thread/Entry[1]/@ID') FROM THREAD t

It’s really as simple as that!

If you are new to the concepts of XPath, why not have a look at the tutorial at W3Schools. It’s all nice and free!