Using XML With DB2 for i
How to Use XML Data Type With DB2 for IBM i
In Part 1 of this article, I provided an overview of the extensible markup language (XML) and shared how XML and relational data can form a partnership. Having a native XML type in DB2 can provide an integrated solution for working with both XML and relational data in the same database.
In Part 2, I want to share the practical steps for how to Parse, Insert, Validate and Serialize XML using DB2 for IBM i, a new enhancement in 7.1.
Storing XML in a DB2 Column
Let’s look at an example of how to put an XML value in a DB2 column. Consider first the case where I simply want to store Order XML documents in an SQL column. This is ideal when I need to retrieve the entire XML document exactly as it was received, perhaps for auditing purposes. It would also be a necessity if shredding the XML document into a traditional relational format proved impractical.
Step 1 is to create a table that has a column with the XML type:
create table order_records (
order_number bigint generated always as identity
(start with 1 increment by 1 nocycle),
order_doc xml ,
primary key (order_number));
I created a primary key using an identity column. The XML data type can’t be compared to any data type, including XML, therefore it can’t be a primary key. Armed with an understanding of XML documents, it’s easy to see why. A comparison between two XML values that may contain different structures and data types isn’t something that can be universally defined.
Step 2 is to create an XML value and insert it into the table. It’s possible to do this with a single insert, but I used a procedure and broke this process up into multiple steps to illustrate a few important details. I’ll demonstrate the easier solution later.
1 create or replace procedure load_xml_from_blob(in_blob blob)
2 language sql
4 declare xmlvalue xml;
5 set xmlvalue = xmlparse(document in_blob);
6 insert into order_records (order_doc) values (xmlvalue);
The serialized XML document that is the input to the procedure is represented as character data. Therefore, on line 1, the procedure accepts a binary large object (BLOB). Most developers would naturally want to use a character large object (CLOB) for this, but this approach can be problematic.