XML functions in DB2 for z/OS

Two directions: publish and shred

Almost every XML task is one of these:

• Publish — relational rows become XML (XMLELEMENT, XMLFOREST, XMLATTRIBUTES, XMLCONCAT, XMLDOCUMENT, XMLCOMMENT, XMLPI, XMLTEXT).
• Parse — a string becomes an XML value (XMLPARSE, often with DSN_XMLVALIDATE).
• Query — extract nodes or values (XMLQUERY in SELECT, XMLEXISTS in WHERE).
• Shred — repeating XML becomes a table (XMLTABLE in FROM).
• Serialize — XML becomes text or bytes for the client (XMLSERIALIZE, or implicit serialize on FETCH).

XML values are not strings. You cannot CONCAT an XML column with LASTNAME. You serialize first, or stay in XML constructors.

Constructor functions

XMLNAMESPACES is a declaration used as an argument of XMLELEMENT or XMLFOREST, not a standalone SELECT-list function. XMLAGG is an aggregate that concatenates XML values across a group — the GROUP BY cousin of XMLCONCAT.

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT XMLELEMENT(
         NAME "emp",
         XMLNAMESPACES(DEFAULT 'http://example.org/hr'),
         XMLATTRIBUTES(EMPNO AS "id"),
         XMLCOMMENT(' sample employee '),
         XMLFOREST(
           RTRIM(FIRSTNME) AS "first",
           RTRIM(LASTNAME) AS "last",
           SALARY         AS "salary"
         )
       ) AS EMP_XML
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010';

To INSERT that result into an XML column, wrap it:

1
2
3
4
5
6
7
8
INSERT INTO HR.EMP_DOC (EMPNO, INFO)
VALUES ('000010',
        XMLDOCUMENT(
          XMLELEMENT(NAME "emp",
            XMLATTRIBUTES('000010' AS "id"),
            XMLFOREST('CHRISTINE' AS "first", 'HAAS' AS "last")
          )
        ));

XMLCONCAT

XMLCONCAT takes a variable number of XML arguments and returns one sequence. Null arguments are skipped in the concatenation of XML values (unlike character CONCAT, where a null operand nulls the whole result). Use it to glue sibling elements you built separately.

1
2
3
4
5
6
SELECT XMLCONCAT(
         XMLELEMENT(NAME "first", RTRIM(FIRSTNME)),
         XMLELEMENT(NAME "last",  RTRIM(LASTNAME))
       )
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010';

XMLCOMMENT, XMLPI, XMLTEXT

• XMLCOMMENT(string) — a comment node. The string must be valid comment content (no -- surprises that break XML well-formedness).
• XMLPI(NAME target, string) — a processing instruction, for example a stylesheet hint.
• XMLTEXT(string) — a text node. Useful when you need text without wrapping another element.

XMLPARSE

XMLPARSE parses a character or binary string as XML and returns an XML value.

1
XMLPARSE(DOCUMENT string-expression [STRIP WHITESPACE | PRESERVE WHITESPACE])

• STRIP WHITESPACE — default in many contexts; boundary whitespace between elements is removed.
• PRESERVE WHITESPACE — keep formatting whitespace for round-trip display.

Implicit parse happens when you INSERT a string host variable into an XML column. Use explicit XMLPARSE when you need whitespace control or validation:

1
2
3
INSERT INTO HR.EMP_DOC (EMPNO, INFO)
VALUES (:EMPNO,
        XMLPARSE(DOCUMENT :XML-TEXT STRIP WHITESPACE));

DSN_XMLVALIDATE may be invoked as an argument of XMLPARSE. When you do that, IBM requires STRIP WHITESPACE.

XMLSERIALIZE

XMLSERIALIZE turns an XML value into a serialized string of a declared type.

1
2
3
XMLSERIALIZE(CONTENT xml-expression AS CLOB(2M)
             [VERSION '1.0']
             [EXCLUDING XMLDECLARATION | INCLUDING XMLDECLARATION])

• CONTENT — serialize the XML value (must not contain a lone attribute node).
• AS data-type — CLOB, BLOB, VARCHAR, and related types; the length must be large enough or you get SQLSTATE 22001.
• VERSION '1.0' — the only supported version, specified as a constant.
• EXCLUDING XMLDECLARATION (default) versus INCLUDING XMLDECLARATION — whether to emit <?xml version="1.0" ...?>.

Implicit serialization on FETCH into an XML or string host variable is usually preferred: the Db2 client handles encoding. Explicit XMLSERIALIZE is for when SQL must produce a CLOB/VARCHAR, for example to CONCAT with other text or to write a file. The old XML2CLOB(expr) spelling is a compatibility synonym for XMLSERIALIZE(... AS CLOB(2G)).

XMLQUERY

XMLQUERY evaluates an XQuery expression and returns XML. You pass the document (and optional variables) with PASSING.

1
2
3
4
SELECT EMPNO,
       XMLQUERY('$d/emp/last' PASSING INFO AS "d") AS LAST_XML
  FROM HR.EMP_DOC
 WHERE XMLEXISTS('$d/emp[@id="000010"]' PASSING INFO AS "d");

PASSING names become XQuery variables without the leading $. The name must be an XML 1.0 NCName, at most 128 bytes, unique in that PASSING clause. Arguments may be XML, integer, decimal, or non-LOB character/graphic strings — not ROWID, TIMESTAMP, binary, REAL, DECFLOAT, or FOR BIT DATA.

Practice: put indexable XPath in XMLEXISTS to qualify rows. Use XMLQUERY (including FLWOR if you must construct new XML) only on the surviving rows. XMLQUERY itself is not indexable the way XMLEXISTS XPath can be.

XMLTABLE

XMLTABLE is a table function. It belongs in FROM. A row XPath identifies repeating nodes; COLUMNS maps each node to SQL types.

1
2
3
4
5
6
7
8
9
10
SELECT X.LINE_ID, X.ITEM, X.QTY
  FROM ORDERS O,
       XMLTABLE(
         '$d/Order/OrderLine'
         PASSING O.ORDER_XML AS "d"
         COLUMNS
           LINE_ID INTEGER PATH '@id',
           ITEM    VARCHAR(40) PATH 'Item/Name',
           QTY     INTEGER PATH 'Qty'
       ) AS X;

If the source is a character string, XMLPARSE it to XML before PASSING. XMLTABLE column expressions can use XQuery to shape values; keep the row expression simple XPath when you want XML index matching.

Nulls and empty sequences

Constructor functions typically omit null relational inputs rather than emitting an empty element (XMLFOREST skips nulls). XMLQUERY can return an empty sequence; EMPTY ON EMPTY (default in the usual form) keeps that as empty XML, not SQL null. Know which you are testing: IS NULL on an XML column versus XMLEXISTS that the path found a node.

Explain It Like I'm Five

XML is a nesting-doll letter. Constructor functions fold paper into dolls (elements, attributes, comments). XMLPARSE takes a written letter and folds it into a real doll. XMLSERIALIZE unfolds the doll back into writing so a COBOL program can read it. XMLQUERY is asking “what is written on the smallest doll?” XMLTABLE dumps every small doll onto a spreadsheet row. XMLDOCUMENT is the envelope: Db2 will not file the letter in the XML drawer without an envelope.

Exercises

1. Write XMLELEMENT + XMLATTRIBUTES + XMLFOREST to publish EMPNO, FIRSTNME, and LASTNAME as one employee element.
2. Explain why XMLDOCUMENT is needed before INSERT into an XML column.
3. Contrast XMLPARSE STRIP WHITESPACE with PRESERVE WHITESPACE for a pretty-printed document.
4. Write XMLEXISTS to find documents with emp id 000010, then XMLQUERY to return the last name node.
5. Sketch XMLTABLE columns that shred order lines into LINE_ID, ITEM, QTY.