XML publishing and constructors in DB2

Parsing vs publishing vs serialization

Three processes surround an XML column:

• XML parsing — textual XML becomes an instance of the pureXML data model (XMLPARSE, or implicit parse when you insert a string/binary/XML host value)
• XML publishing / constructors — SQL builds XML nodes from relational expressions (XMLELEMENT and family) without ever having had a text document
• XML serialization — the internal tree becomes textual XML (XMLSERIALIZE, or implicit serialize into a string/binary/XML application variable)

You can mix them: parse a message, extract pieces with XMLTABLE, then publish a new response document with XMLELEMENT. Validation (previous page) can sit on the insert of either parsed or constructed XML.

XMLPARSE

XMLPARSE(DOCUMENT string-expression [STRIP WHITESPACE | PRESERVE WHITESPACE]) parses the argument as an XML document and returns XML. DOCUMENT means the character or BLOB value must be a well-formed XML 1.0 document (SQLSTATE 2200M if not).

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO ORDER_XML (ORDER_ID, ORDER_DOC)
VALUES (
  1001,
  XMLPARSE(
    DOCUMENT CAST(
      'WIDGET'
      AS CLOB
    )
    STRIP WHITESPACE
  )
);

If any text node begins with more than 1000 bytes of whitespace under STRIP WHITESPACE, Db2 returns an error (SQLSTATE 54059). Use PRESERVE WHITESPACE for document types that treat whitespace as significant (some mixed-content publishing formats).

Encoding trap: a character-string argument is converted to the database server code page. That page may not match the encoding named in the XML declaration. IBM recommends sending documents as BLOB (or XML host variables) so the bytes are not recoded underneath the declaration. If you must XMLPARSE a character value, make sure the declaration and the actual bytes agree after conversion.

A parameter marker used with XMLPARSE must be explicitly cast to a supported string or BLOB type. Null input yields a null XML result.

XMLSERIALIZE

XMLSERIALIZE(CONTENT xml-expression AS data-type ...) returns a serialized XML value of the type you name. CONTENT means any XML value is allowed (except an attribute node). The result length must be large enough or you get a truncation/error (SQLSTATE 22001).

• AS CLOB / DBCLOB / BLOB / VARCHAR / ... — pick a type your program can fetch; length must hold the output
• VERSION '1.0' — the only supported serialization version; specify it as a string constant
• EXCLUDING XMLDECLARATION — default; no <?xml ...?>
• INCLUDING XMLDECLARATION — declaration for version 1.0 and encoding UTF-8

1
2
3
4
5
6
7
8
SELECT ORDER_ID,
       XMLSERIALIZE(
         CONTENT ORDER_DOC AS CLOB(1M)
         VERSION '1.0'
         INCLUDING XMLDECLARATION
       ) AS ORDER_TEXT
  FROM ORDER_XML
 WHERE ORDER_ID = 1001;

A sequence is normalized to a single document node as if you called XMLDOCUMENT first. These two expressions serialize the same way:

1
2
XMLSERIALIZE(S AS CLOB)
XMLSERIALIZE(XMLDOCUMENT(S) AS CLOB)

XML2CLOB(xml-expression) is a compatibility alternative to XMLSERIALIZE(xml-expression AS CLOB(2G)). Prefer XMLSERIALIZE in new SQL so the target type and declaration options are obvious. Serialized XML in or out of an XML column is limited to about 2 GB.

Implicit serialization happens on FETCH into CHAR/VARCHAR/CLOB host variables. Explicit XMLSERIALIZE is better when you need the declaration, a specific CCSID/type, or to store text in a CLOB column instead of XML.

XMLDOCUMENT

XMLDOCUMENT returns an XML value that is a single document node with zero or more children from its argument sequence. Use it when an API or XMLPARSE/DOCUMENT-style consumer requires a document, not a bare element or a sequence of siblings.

1
2
3
XMLDOCUMENT(
  XMLELEMENT(NAME "order", XMLATTRIBUTES(O.ORDER_ID AS "id"))
)

XMLSERIALIZE already applies this wrapping. You still write XMLDOCUMENT when you store constructed XML in an XML column that conceptually holds documents, or when XQuery expects a document node as context.

Constructor functions (publishing)

Constructors build XML from SQL expressions. Names in NAME clauses are XML QNames. Null handling options on XMLELEMENT include EMPTY ON NULL (empty element) versus NULL ON NULL (omit / null XML depending on context)— know which your shop wants for optional columns.

XMLELEMENT and XMLATTRIBUTES

XMLELEMENT(NAME element-name, ...) creates one element. Optional arguments, in order: XMLNAMESPACES declaration, XMLATTRIBUTES, then content expressions. XMLATTRIBUTES may appear only inside XMLELEMENT.

1
2
3
4
5
6
7
8
SELECT XMLELEMENT(
         NAME "emp",
         XMLATTRIBUTES(E.EMPNO AS "id", E.WORKDEPT AS "dept"),
         XMLELEMENT(NAME "name", E.LASTNAME),
         XMLELEMENT(NAME "salary", E.SALARY)
       ) AS EMP_XML
  FROM DSN8C10.EMP E
 WHERE E.WORKDEPT = 'A00';

Attribute names come from AS names or from the SQL column name. Content expressions become child nodes (or text). Nested XMLELEMENT calls build a tree. OPTION clauses can control binary encoding (XMLBINARY USING BASE64 or HEX) when content is binary.

XMLFOREST

XMLFOREST takes a list of expressions and returns a sequence of element nodes, one per expression. Element names default from column names or AS aliases. It is a compact way to emit several siblings without nesting a stack of XMLELEMENT calls.

1
2
3
4
5
XMLFOREST(
  E.FIRSTNME AS "first",
  E.LASTNAME AS "last",
  E.JOB      AS "job"
)

XMLFOREST can take XMLNAMESPACES as well. Null expressions typically omit that element (forest semantics skip nulls)—verify against your Db2 version’s null option if you need empty tags instead.

XMLCONCAT

XMLCONCAT concatenates a variable number of XML arguments into one sequence. Use it in a single row when you have several XML pieces (for example a header element plus a forest of details) that should sit side by side.

1
2
3
4
XMLCONCAT(
  XMLELEMENT(NAME "header", E.EMPNO),
  XMLFOREST(E.LASTNAME AS "last", E.JOB AS "job")
)

XMLCONCAT is not an aggregate. For many rows, use XMLAGG.

XMLAGG

XMLAGG(xml-expression [ORDER BY sort-key]) is the XML aggregate. It returns a sequence with one item per non-null XML value in the group. ORDER BY inside XMLAGG controls sibling order; without it, order is arbitrary. Sort keys must not be LOB or XML, and a character sort key cannot exceed 4000 bytes. A constant sort key does not mean “column position” the way SELECT ORDER BY 1 does.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT XMLSERIALIZE(
         XMLDOCUMENT(
           XMLELEMENT(
             NAME "Department",
             XMLATTRIBUTES(E.WORKDEPT AS "name"),
             XMLAGG(
               XMLELEMENT(NAME "emp", E.LASTNAME)
               ORDER BY E.LASTNAME
             )
           )
         ) AS CLOB(1M)
       ) AS DEPT_LIST
  FROM DSN8C10.EMP E
 GROUP BY E.WORKDEPT;

Empty group / all nulls: XMLAGG returns null. You cannot mix XMLAGG with ARRAY_AGG or LISTAGG in the same SELECT list. XMLAGG is not part of an OLAP specification.

XMLNAMESPACES

XMLNAMESPACES builds namespace declarations. Use DEFAULT 'uri' for a default namespace, or prefix AS 'uri' pairs. Scope is the XMLELEMENT or XMLFOREST that contains the declaration, including nested calls in that lexical scope. Prefixes in NAME QNames must be declared.

1
2
3
4
5
6
7
XMLELEMENT(
  NAME "order",
  XMLNAMESPACES(DEFAULT 'http://example.org/po',
                'http://example.org/addr' AS "a"),
  XMLATTRIBUTES(O.ORDER_ID AS "id"),
  XMLELEMENT(NAME "a:shipTo", O.CITY)
)

XMLCOMMENT, XMLPI, XMLTEXT

• XMLCOMMENT(string) — one comment node; content must be legal comment text (no -- surprises)
• XMLPI(NAME target, string) — processing instruction node (for example stylesheet hints). NAME is the PI target
• XMLTEXT(string) — a text node; useful when you need an explicit text node rather than letting XMLELEMENT stringify an argument

1
2
3
4
5
6
XMLELEMENT(
  NAME "note",
  XMLCOMMENT('generated from EMP'),
  XMLPI(NAME "hint", 'priority=normal'),
  XMLTEXT(E.LASTNAME)
)

XMLROW (platform note)

On Db2 LUW and Db2 for i, XMLROW publishes each relational row as an XML element with child elements or attributes from the column list. Db2 for z/OS does not document XMLROW in the SQL Reference constructor set. Emulate it:

1
2
3
4
5
-- z/OS equivalent of "one element per row"
XMLELEMENT(
  NAME "row",
  XMLFOREST(E.EMPNO AS "empno", E.LASTNAME AS "lastname")
)

Then XMLAGG those row elements under a parent if you need a document of many rows. Keep this distinction in mind when copying LUW examples onto z/OS.

A full publishing query

Putting constructors together with GROUP BY:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT XMLSERIALIZE(
         CONTENT
         XMLDOCUMENT(
           XMLELEMENT(
             NAME "saleProducts",
             XMLNAMESPACES(DEFAULT 'http://posample.org'),
             XMLAGG(
               XMLELEMENT(
                 NAME "prod",
                 XMLATTRIBUTES(P.PID AS "id"),
                 XMLFOREST(P.NAME AS "name", P.PRICE AS "price")
               )
               ORDER BY P.PID
             )
           )
         )
         AS CLOB(2M)
         INCLUDING XMLDECLARATION
       ) AS CATALOG_XML
  FROM PRODUCT P
 WHERE P.IN_STOCK > 0;

That is the publishing loop: filter rows, build a per-row element, aggregate into a parent, wrap as a document, serialize for the caller. Store the XMLDOCUMENT result in an XML column if the catalog is an XML payload; serialize only at the edge.

XML functions map

Beginners mix constructor names with query names. Quick split:

• Query — XMLQUERY, XMLEXISTS, XMLTABLE, XMLCAST (previous page)
• Parse / serialize — XMLPARSE, XMLSERIALIZE, XMLDOCUMENT
• Publish — XMLELEMENT, XMLATTRIBUTES, XMLFOREST, XMLCONCAT, XMLAGG, XMLNAMESPACES, XMLCOMMENT, XMLPI, XMLTEXT
• Validate — DSN_XMLVALIDATE / type modifiers (schema page)

All of these return or consume the XML data type except XMLSERIALIZE (returns a string or binary type) and XMLEXISTS (predicate). Constructor XML does not have to be well-formed as a complete document until you wrap it with XMLDOCUMENT or serialize under CONTENT rules.

Explain It Like I'm Five

Parse is opening a folded paper castle and snapping it into Lego bricks (the XML tree). Serialize is folding the Lego back into paper so you can mail it. Constructors skip the paper: you pick bricks from your employee table and snap a new castle (XMLELEMENT is one room, XMLATTRIBUTES are stickers on the door, XMLFOREST is several small rooms in a row, XMLCONCAT tapes rooms together, XMLAGG gathers every kid’s room into one hallway). XMLDOCUMENT puts a roof on the whole house. XMLCOMMENT is a sticky note that is not a room.

Exercises

1. Write XMLPARSE DOCUMENT for a small <hi/> document stored in a CLOB expression, using STRIP WHITESPACE.
2. Serialize an XML column as CLOB(1M) with INCLUDING XMLDECLARATION and VERSION '1.0'.
3. Build XMLELEMENT NAME "dept" with XMLATTRIBUTES(WORKDEPT AS "id") and a nested name element from a department description column.
4. Explain when you would use XMLCONCAT versus XMLAGG.
5. Rewrite a hypothetical LUW XMLROW(EMPNO, LASTNAME) as z/OS XMLELEMENT + XMLFOREST.