Db2 XML type overview

What the XML type is

The XML data type defines columns that store XML values. This pureXML type provides the ability to store well-formed XML documents in the database. When you create a table with an XML column, Db2 implicitly creates an XML table space and an XML table to hold the XML data.

1
2
3
4
5
CREATE TABLE ORDER_XML (
    ORDER_ID   INTEGER     NOT NULL,
    ORDER_DOC  XML         NOT NULL,
    PRIMARY KEY (ORDER_ID)
);

The business key remains relational (ORDER_ID). The document lives in the XML column with XML storage behind the scenes—similar in spirit to how LOBs use auxiliary storage, but specialized for XML.

Internal representation vs strings

All XML data is stored in an internal representation. Character data in that form uses UTF-8 in IBM’s description of the internal encoding. Critically: XML values are not a string type and are not directly comparable to ordinary string values.

To get a serialized document (text form of the XML), use XMLSERIALIZE or retrieve into an application variable of XML, string, or binary type. To store a document from text, use XMLPARSE or insert from a string, binary, or XML application type into the XML column.

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO ORDER_XML (ORDER_ID, ORDER_DOC)
VALUES (
  1001,
  XMLPARSE(DOCUMENT CAST(
    'WIDGET'
    AS CLOB)
  )
);
 
SELECT ORDER_ID,
       XMLSERIALIZE(ORDER_DOC AS CLOB) AS ORDER_TEXT
  FROM ORDER_XML
 WHERE ORDER_ID = 1001;

Exact XMLPARSE options (DOCUMENT vs CONTENT, STRIP WHITESPACE, and so on) belong in deeper XML SQL lessons. Mentally: parse in, serialize out when crossing the string/XML boundary.

Size and validation

The size of an XML value in a Db2 table has no architectural limit in IBM’s wording. However, serialized XML stored in or retrieved from an XML column is limited to 2 GB. Plan application buffers and network transfers accordingly.

Validation against an XML schema—often during INSERT or UPDATE—is supported through the XML schema repository (XSR). If a column requires validated XML, documents must pass schema checks. Unvalidated storage is also possible depending on column definition; choose based on how strictly you trust producers.

XML indexes (awareness)

Db2 supports XML indexes for efficient access to parts of documents. Like relational indexes, they help some queries and cost maintenance on write—design with EXPLAIN and workload in mind.

XML type vs CLOB vs shredding

• XML type — query with XML functions, validate, index XML structure
• CLOB — opaque text blob of markup; little XML intelligence inside the engine
• Shredding — map elements to columns for relational integrity and simple SQL

Many shops combine approaches: store the canonical document in XML (or CLOB) and maintain relational summary tables for joins and constraints. Pick based on access patterns, not hype.

Programming tip

Host languages need XML-capable variables or string buffers large enough for serialized documents. Fetching enormous documents into COBOL working storage is rarely wise—stream, locate, or extract nodes with XMLQUERY/XMLTABLE-style facilities in later lessons.

Explain It Like I'm Five

An XML document is a nested set of labeled boxes inside boxes. The XML type is a special shelf built for those boxes so the librarian can open them intelligently. A CLOB shelf just stores a long paper strip of writing. To put paper on the special shelf you parse it into boxes; to take it home as paper you serialize boxes back into writing.

Exercises

1. Write a CREATE TABLE with an integer key and an XML column.
2. In one sentence each, define XMLPARSE and XMLSERIALIZE.
3. Why might comparing an XML column directly to a VARCHAR literal be the wrong tool?
4. Give one reason to prefer the XML type over a CLOB of XML text.
5. What storage objects can Db2 create implicitly for an XML column?