Using XML With DB2 for i

How to Use XML Data Type With DB2 for IBM 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
3 begin
4 declare xmlvalue xml;
5 set xmlvalue = xmlparse(document in_blob);
6 insert into order_records (order_doc) values (xmlvalue);
7 end;

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.

Nick Lawrence works for IBM in Rochester, Minn., on DB2 for IBM i.

comments powered by Disqus



2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Are You Multilingual?

Rational enables development in multiplatform environments

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters