Coding Insomnia

As a Microsoft junkie, I haven’t really used (or even bothered looking at) the Oracle database suite before. However, since it is the main database used by Observer, I’ve spent some time looking at the features of it – and I must say I’m pretty impressed.

Did you know, for example, that Oracle has had support for storing XML data in a typed storage since version 9.2? This is something that is being added to SQL Server now in the Yukon release (SQL Server 2005) if I’m not completely mistaken. And it’s pretty good stuff! Let me explain a little bit of how it works..

First of all, you need to define an XML Schema (XSD) of the XML you want to store. You can do this with pretty much any XML tool, but I’ve come to prefer Altova XMLSpy (check out the free Home edition, it’s really all you need in most cases..)

Next, you register this schema in Oracle, and you give it a URL (I think it makes sense to use the URL of the schema itself). This URL is later on used when you add XML instances, but more on that later. When registering the schema, Oracle automagically creates a whole load of types, tables, arrays etc for storing instances of your XML documents without actually storing the XML string itself.

After successfully registering a schema, you can add tables or columns that are based on the XMLType. Now, of course you can do this the hard way by issuing a couple of PL/SQL statements, but since I’m a complete n00b on Oracle, I prefer doing it the easy way via the Oracle Enterprise Manager GUI.. If you do it my way, you add a column, set the type to be “XMLType” and in the new tab that appears, you select your newly registered schema.

Now, you are ready to add some XML to your table! The one thing you need to keep in mind is that the root element of the XML you’re adding must refer to the XML Schema you’ve just registered. Let’s say that you used a schema with no targetNamespace, and you registered it with the URL “http://ljusberg.com/xsd/test.xsd”. This means that the XML you add must have the following attributes on the root element:

<Root
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://ljusberg.com/xsd/test.xsd">
... rest of the xml document goes here ...
</Root>

You can then add this by a simple INSERT statement. Something like this should do the trick:

INSERT INTO MY_TABLE (my_id, my_xml_column)
VALUES (MY_SEQ.NEXTVAL, XMLType('<Root
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://ljusberg.com/xsd/test.xsd">
... rest of the xml document goes here ...
</Root>'))

Tada! Now you’ve added some XML to your table, pretty easy, isn’t it? But this is when you can start doing some really cool stuff, such as selecting rows from your table by an XPath expression! But that will have to wait until my next post.

If you want to know more about the inner workings of structured storage of an XMLType, check out this article from the Oracle documentation!