blog.mycado.fr

Store native XML in a DB2 table

IBM DB2 is the first RDBMS to provide a native XML facility, in a table. You will be able to insert in a column, some XML data. It will be easier to make direct requests to these data with XQuery.

So, it’s funny, but why it’s interesting to do that ? Because XML data are hierarchic (instead of relational data which are flat) and data self-describing through XML tags. XML also allows a better flexibility for data structures required to change very often. In the other hand, access time performances will be a little slower, we lose the integrity constraints, and OLAP queries will be more difficult. The important question is “Which flexibility-performance ratio do you need ?”.

Let’s start now with the creation of a database, which should be encoded with UTF-8, to store XML. For that, we use the CREATE DATABASE command:

CREATE DATABASE xmldb USING CODESET UTF-8 TERRITORY US

We have our table, now we should create the “client” table, with an “info” column who contain client information, in XML format:

CONNECT TO xmldb
CREATE TABLE client (id INT, info XML)

Let’s try to insert a new client with a SQL query:

INSERT INTO client (id, info) VALUES (1, '<clientinfo xmlns="http://posample.org" Cid="1"><name>Sophie Bool</name><addr country="France"><street>5 rue du chateau de stable</street><city>Paris</city></addr><phone type="work">01 72 92 02 88</phone></clientinfo>')

The first thing you will told me is this query is a normal SQL query, and this is right, insert XML isn’t more difficult. The second thing is about the XML, here we have a short XML data, but if we have more, it will be very difficult to use. This why, we will use XQuery to manipulate these data or use a XDS import. I’ll come back soon on these points.