DB2 with XML

DB2 - Working with XML and JSON Data

Introduction to Multi-Model Data in Db2

Modern applications often deal with semi-structured data that doesn’t fit neatly into traditional rows and columns. Db2 is a powerful multi-model database that offers native support for storing and querying this kind of data, most notably through its pureXML feature and robust JSON capabilities. Storing this data natively, rather than as plain text in a CLOB, allows the database to parse, index, and query the data efficiently.

This tutorial focuses on the XML data type, which allows you to store well-formed XML documents directly in a table column.

Creating Tables with an XML Column

To store XML data, you simply define a column with the XML data type during table creation.

Example:

-- First, ensure you are connected to your database
-- db2 connect to mydatabase

-- Create a table to store customer information, including an XML doc for their profile
CREATE TABLE customers (
  id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  registered_date DATE NOT NULL DEFAULT CURRENT_DATE,
  profile XML
);

Inserting and Updating XML Data

You can insert XML data using a standard INSERT statement, passing the XML document as a string. Db2 will parse it to ensure it is well-formed.

Example: Inserting a new customer profile.

INSERT INTO customers (profile) VALUES(
  XMLPARSE(DOCUMENT
    '<profile>
      <name>John Doe</name>
      <email>john.doe@example.com</email>
      <preferences>
        <newsletter>true</newsletter>
        <type>premium</type>
      </preferences>
    </profile>'
  )
);

While you can update an entire XML document with a standard UPDATE statement, it’s more efficient to modify only the parts that change. This can be done with an XQuery Update expression.

Example: Updating the customer’s email address.

UPDATE customers
SET profile = XMLQUERY(
  'transform
   copy $new_profile := $doc
   modify do replace value of $new_profile/profile/email with "john.d@new-example.com"
   return $new_profile'
  PASSING profile AS "doc"
)
WHERE id = 1;

Querying XML Data with SQL/XML

The true power of the XML data type comes from the ability to query its contents using SQL/XML functions. These functions bridge the gap between the relational world of SQL and the hierarchical world of XML.

XMLEXISTS: Checking for the Existence of Data

Use XMLEXISTS in a WHERE clause to find rows based on the content of the XML document.

-- Find all customers who are subscribed to the newsletter
SELECT id, registered_date
FROM customers
WHERE XMLEXISTS('$p/profile/preferences[newsletter="true"]' PASSING profile AS "p");

XMLQUERY: Extracting XML Fragments

Use XMLQUERY to extract a fragment or a value from the XML column.

-- Get the email address for customer with ID 1
SELECT XMLCAST(XMLQUERY('$p/profile/email/text()' PASSING profile AS "p") AS VARCHAR(100))
FROM customers
WHERE id = 1;

XMLTABLE: Shredding XML into a Relational Format

Use XMLTABLE to project values from your XML documents into a relational result set, making it look like a regular table. This is incredibly useful for reporting.

-- List all customers with their name and preference type
SELECT C.id, T.*
FROM customers C,
     XMLTABLE('$p/profile' PASSING C.profile AS "p"
       COLUMNS
         customer_name VARCHAR(100) PATH 'name',
         preference_type VARCHAR(20) PATH 'preferences/type'
     ) AS T;

Modern Alternative: Working with JSON

While XML is powerful and widely used in enterprise systems, many new applications, especially web and mobile, prefer JSON (JavaScript Object Notation) for its simplicity and lighter weight. Db2 has excellent support for storing and querying JSON data. Data is stored in its binary representation (BSON) for efficiency.

You can use SQL/JSON functions like JSON_VALUE, JSON_QUERY, and JSON_TABLE, which are analogous to their XML counterparts, to work with JSON data stored in VARCHAR, BLOB, or CLOB columns. If you are starting a new project, consider whether JSON is a better fit for your semi-structured data needs.

For more information, search the Db2 Knowledge Center for “JSON capabilities in Db2”.