Write XML SQL in DB2 for z/OS

Prerequisites and the XML data model

You should be comfortable with SELECT, INSERT, UPDATE, joins, host variables, and ordinary Db2 authorization. The examples use an order table whose relational columns support efficient identity and status searches while its document column retains the hierarchical order payload. In a real subsystem, confirm the Db2 function level, application compatibility level, object privileges, Unicode requirements, and XML table-space design with the database administrator.

1
2
3
4
5
6
CREATE TABLE SALES.XML_ORDER
 (ORDER_ID    BIGINT NOT NULL,
  CUSTOMER_ID INTEGER NOT NULL,
  STATUS      CHAR(10) NOT NULL,
  ORDER_DOC   XML,
  PRIMARY KEY (ORDER_ID));

Every non-null value stored in ORDER_DOC must be a well-formed XML document. The XML value is not directly comparable with =, <, LIKE, or ordinary string predicates. IBM documents XMLEXISTS and the NULL predicate as the predicates that apply to the XML type. Keep keys, frequently joined values, and operational status relational when that shape is stable. Use XML for the hierarchical portion rather than hiding every searchable attribute in one document.

Step 1: parse incoming XML text

XMLPARSE transforms a character, graphic, or binary representation into the XML data type. The DOCUMENT keyword says the input must be one well-formed document. A parameter marker should be cast to a known string type when Db2 cannot infer the intended type. Parameterization also keeps document text separate from SQL syntax.

1
2
3
4
5
6
7
8
9
INSERT INTO SALES.XML_ORDER
       (ORDER_ID, CUSTOMER_ID, STATUS, ORDER_DOC)
VALUES (10001,
        501,
        'NEW',
        XMLPARSE(
          DOCUMENT CAST(:ORDER_XML AS CLOB(2M))
          PRESERVE WHITESPACE
        ));

PRESERVE WHITESPACE retains boundary whitespace according to XML parsing rules; STRIP WHITESPACE removes ignorable boundary whitespace where supported by the expression. Choose deliberately if whitespace-only text nodes affect downstream XQuery. XMLPARSE verifies well-formedness, including matching tags, legal characters, and one document root. It does not by itself prove that required business elements exist or that the document conforms to an XML Schema.

Step 2: serialize XML for an application

XMLSERIALIZE performs the opposite boundary conversion. It converts Db2's internal XML value to a character or binary result that a program, report, or interface can consume. Select a target type large enough for the result. Serialization can fail if the target is too short or the requested encoding cannot represent the content.

1
2
3
4
5
6
SELECT XMLSERIALIZE(
         ORDER_DOC AS CLOB(2M)
       )
  INTO :ORDER_XML_CLOB
  FROM SALES.XML_ORDER
 WHERE ORDER_ID = :ORDER_ID;

Do not serialize merely to search the resulting CLOB with LIKE. That discards XML structure, can mishandle namespaces and entities, and prevents XML-aware optimization. Keep the value as XML while querying it, and serialize only at the boundary where text is actually required.

Step 3: retrieve fragments with XMLQUERY and XQuery

XMLQUERY evaluates an XQuery expression and returns XML. The PASSING clause binds SQL values to external XQuery variables. In this example, the SQL column becomes the XQuery variable $doc. The XQuery navigates from the document node to the customer element and returns that element, preserving XML markup.

1
2
3
4
5
6
7
SELECT ORDER_ID,
       XMLQUERY(
         '$doc/order/customer'
         PASSING ORDER_DOC AS "doc"
       ) AS CUSTOMER_XML
  FROM SALES.XML_ORDER
 WHERE ORDER_ID = 10001;

XQuery is case-sensitive. Element order and hierarchy matter, and a path that finds no item produces an empty result rather than an SQL row filter. Use XMLQUERY when the required output is XML: an element, a reconstructed fragment, or a sequence. If an application needs VARCHAR, INTEGER, DATE, or DECIMAL columns, XMLTABLE is usually the clearer bridge.

XQuery can contain path expressions, predicates, variables, conditional expressions, and FLWOR expressions. Start with the smallest expression that answers the requirement. Bind changing values as external variables rather than concatenating them into the query text. This improves safety and avoids quoting errors.

Step 4: filter documents with XMLEXISTS

XMLEXISTS is true when its XQuery expression returns at least one item. It belongs in a WHERE or other predicate context and is the natural way to eliminate documents that do not match. IBM's performance guidance recommends document-level filtering with XMLEXISTS instead of extracting every result with XMLQUERY and then testing empty output.

1
2
3
4
5
6
7
SELECT ORDER_ID, CUSTOMER_ID, STATUS
  FROM SALES.XML_ORDER
 WHERE XMLEXISTS(
         '$doc/order[totalAmount > $minimum]'
         PASSING ORDER_DOC AS "doc",
                 :MINIMUM_AMOUNT AS "minimum"
       );

The external variable keeps the amount out of the XQuery string. Db2 can compare the atomic value represented by the totalAmount element with the supplied value, subject to XQuery conversion rules. Dirty or unexpectedly typed content can still cause errors, so schema validation and test documents matter. XMLEXISTS answers whether a match exists; it does not return the matching node.

Step 5: convert repeating XML to rows with XMLTABLE

XMLTABLE maps an XQuery result to a relational table. Its first expression selects the row items. The COLUMNS clause then evaluates each PATH relative to the current item and casts the result to the declared SQL type. One order with three line elements therefore contributes three relational rows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT O.ORDER_ID,
       X.LINE_POSITION,
       X.SKU,
       X.QUANTITY,
       X.UNIT_PRICE
  FROM SALES.XML_ORDER AS O,
       XMLTABLE(
         '$doc/order/lines/line'
         PASSING O.ORDER_DOC AS "doc"
         COLUMNS
           LINE_POSITION FOR ORDINALITY,
           SKU           VARCHAR(20)  PATH 'sku',
           QUANTITY      INTEGER      PATH 'quantity',
           UNIT_PRICE    DECIMAL(9,2) PATH 'unitPrice'
       ) AS X
 WHERE O.STATUS = 'NEW';

FOR ORDINALITY numbers selected line items, which is useful when document order has meaning. A missing optional item can become null in a nullable output column. A path that produces multiple values where one scalar is expected, malformed numeric text, or a value outside the SQL target range can raise an error. Match every declared SQL type to the XML contract instead of using VARCHAR for everything.

Step 6: declare XML namespaces

Namespaces are a frequent cause of empty results. The source prefix is not the identity; the namespace URI and local name are. If an order document declares a default namespace, unqualified XQuery names do not automatically match it. Declare a prefix with XMLNAMESPACES for XMLTABLE, or declare a namespace in the XQuery prolog for XMLQUERY and XMLEXISTS.

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT O.ORDER_ID, X.SKU, X.QUANTITY
  FROM SALES.XML_ORDER AS O,
       XMLTABLE(
         XMLNAMESPACES(
           'urn:example:order:v1' AS "ord",
           'urn:example:catalog:v1' AS "cat"
         ),
         '$doc/ord:order/ord:lines/ord:line'
         PASSING O.ORDER_DOC AS "doc"
         COLUMNS
           SKU      VARCHAR(20) PATH 'cat:product/cat:sku',
           QUANTITY INTEGER     PATH 'ord:quantity'
       ) AS X;

1
2
3
4
5
6
7
8
SELECT ORDER_ID
  FROM SALES.XML_ORDER
 WHERE XMLEXISTS(
   'declare default element namespace "urn:example:order:v1";
    $doc/order/customer/customerId[. = $id]'
   PASSING ORDER_DOC AS "doc",
           :CUSTOMER_ID AS "id"
 );

A prefix chosen in SQL can differ from the prefix used in the stored document. Both refer to the same expanded name when their URIs match. Attributes are commonly unqualified even when elements use a default namespace, so do not prefix an attribute path unless the document actually places that attribute in a namespace.

Step 7: join relational and XML data

XMLTABLE participates in the FROM clause like another table reference. This makes a mixed design practical: use relational columns to locate candidate orders, expand only their line items, and join a typed SKU to a normalized product table. The optimizer can then combine ordinary access paths with XML processing.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT O.ORDER_ID,
       P.PRODUCT_NAME,
       X.QUANTITY,
       X.QUANTITY * P.CURRENT_PRICE AS CURRENT_VALUE
  FROM SALES.XML_ORDER AS O,
       XMLTABLE(
         '$doc/order/lines/line'
         PASSING O.ORDER_DOC AS "doc"
         COLUMNS
           SKU      VARCHAR(20) PATH 'sku',
           QUANTITY INTEGER     PATH 'quantity'
       ) AS X
  JOIN SALES.PRODUCT AS P
    ON P.SKU = X.SKU
 WHERE O.STATUS = 'NEW'
   AND XMLEXISTS(
         '$doc/order/lines/line[quantity > 0]'
         PASSING O.ORDER_DOC AS "doc"
       );

Apply selective relational predicates as early as the query permits. If an XML condition eliminates entire documents, express it in XMLEXISTS so Db2 can consider a matching XML index. XMLTABLE is excellent for projection, but shredding every document into rows and applying a relational predicate afterward can perform more XML work than necessary.

Step 8: update part of a document with XMLMODIFY

XMLMODIFY is a specialized Db2 for z/OS scalar function for an XML-column assignment in UPDATE. Its XQuery updating expression can replace a node value, insert nodes, or delete nodes. The target XML document is the XML column being assigned; additional SQL expressions can be passed as named XQuery variables.

1
2
3
4
5
6
7
8
9
10
11
UPDATE SALES.XML_ORDER
   SET ORDER_DOC = XMLMODIFY(
         'replace value of node /order/status
          with $newStatus',
         :NEW_STATUS AS "newStatus"
       )
 WHERE ORDER_ID = :ORDER_ID
   AND XMLEXISTS(
         '$doc/order/status'
         PASSING ORDER_DOC AS "doc"
       );

The high-level benefit is partial update. IBM notes that changing only part of a large document can avoid deleting and replacing all of its XML storage rows. Small documents that fit in one record might receive no performance advantage. Updating expressions also have cardinality rules: for example, a replace target normally must identify the required single node. Test missing, duplicate, and namespace-qualified targets.

XMLMODIFY is not a general-purpose expression that can be placed everywhere an XML value is accepted. Keep conditional logic in the UPDATE search condition or separate statements when necessary, and consult the SQL Reference for the exact supported updating expressions at the deployed function level.

XML indexes and performance

A relational index keys table columns. An XML index keys nodes selected from one XML column by an XML pattern. Because several nodes in one document can match, one document can contribute several index keys. Define an XML index for stable, selective paths that appear in important XMLEXISTS predicates, not for every element.

1
2
3
4
5
CREATE INDEX SALES.XML_ORDER_SKU_IX
    ON SALES.XML_ORDER (ORDER_DOC)
GENERATE KEY USING XMLPATTERN
  '/order/lines/line/sku'
  AS SQL VARCHAR(20);

Namespace declarations in the index pattern must describe the same expanded names used by stored documents and queries. The AS SQL type must represent indexed values without unwanted truncation or conversion. After creating or changing indexes, maintain the relevant statistics and use EXPLAIN to verify access rather than assuming a matching- looking path guarantees index use.

• Filter base rows with relational predicates before expanding large documents where the logical query permits it.
• Put document-level XML filtering in XMLEXISTS and use XMLQUERY or XMLTABLE for the values that surviving rows must return.
• Keep paths specific. Descendant searches such as //line are convenient but can examine more nodes and express a weaker contract than a full path.
• Avoid repeatedly serializing, parsing, or shredding the same XML when a relational generated result or normalized column better serves a frequent workload.
• Measure document size, rows selected per document, conversion failures, CPU, and getpage behavior with representative production-like data.

Validation and error handling

Well-formedness and schema validity are different checks. XMLPARSE rejects malformed XML syntax, but an order can be well formed while omitting a required customer or using text where the business schema requires a decimal. Db2 for z/OS provides an XML schema repository. After an XML schema is registered there, DSN_XMLVALIDATE can validate a value explicitly, and an XML type modifier can associate registered schemas with an XML column so stored documents are validated.

1
2
3
4
5
6
7
8
9
INSERT INTO SALES.XML_ORDER
       (ORDER_ID, CUSTOMER_ID, STATUS, ORDER_DOC)
VALUES (:ORDER_ID,
        :CUSTOMER_ID,
        'NEW',
        DSN_XMLVALIDATE(
          CAST(:ORDER_XML AS CLOB(2M)),
          'SYSXSR.ORDER_V1'
        ));

Exact DSN_XMLVALIDATE argument forms depend on whether the schema is identified by its registered object name, target namespace, or schema location. Confirm the form against the registered XSR objects in your subsystem. Schema validation costs work, but it moves structural defects to the ingestion boundary instead of letting a later XMLTABLE cast fail unexpectedly.

1. Reject or quarantine malformed input when XMLPARSE reports an SQL error; do not retry unchanged content indefinitely.
2. Record the SQLCODE, SQLSTATE, operation, document identifier, and a safe correlation ID, but avoid placing the full sensitive document in application logs.
3. Test missing nodes, duplicate scalar nodes, invalid dates and numbers, oversized values, namespace-version changes, null XML, and documents near the maximum expected size.
4. Make transaction behavior explicit. A failed XML conversion or update should follow the same unit-of-work recovery design as the surrounding relational changes.

Security rules for XML SQL

XML can carry personally identifiable information, credentials, payment details, or partner data inside one column. Grant only the table, column, XSR, and update privileges required by the application role. Returning a whole serialized document can expose far more than returning two approved XMLTABLE columns, so projection is part of access control even when SQL authorization succeeds.

• Use parameter markers and PASSING variables. Never construct SQL or XQuery by concatenating untrusted values into statement text.
• Validate accepted document vocabularies and sizes at a trusted boundary. Treat XML from files, queues, APIs, and partner feeds as untrusted input.
• Avoid dynamic external resource behavior. Keep document processing within the features and registered schemas deliberately approved for the application.
• Redact XML in diagnostics, traces, dead-letter queues, and audit records. A single document can contain many independently sensitive fields.
• Review row permissions, column masks, encryption, backup protection, and audit policy for both the relational owner row and its XML storage.
• Place limits on result size. An unconstrained XMLTABLE expression can multiply one relational row into thousands of output rows.

Verify the results

Verification should prove structure, values, cardinality, access path, and recovery behavior. Do not stop after seeing one successful row in SPUFI. Use a small set of known documents that includes namespace-qualified input and edge cases.

1. Insert one known document with XMLPARSE, serialize it, and confirm that the expected element values survive the round trip.
2. Run XMLEXISTS with matching and nonmatching parameter values and verify the exact base rows returned.
3. Count XMLTABLE output by order and compare it with the number of line elements in each source document.
4. Join shredded SKUs to the product table and deliberately include an unknown SKU to confirm inner-versus-outer join behavior.
5. Apply XMLMODIFY inside a rollback-capable test unit of work, query the changed node, and then verify rollback restores the prior document.
6. EXPLAIN the important XMLEXISTS query before and after the XML index, then compare actual workload measurements rather than relying on elapsed time from one execution.

Common errors and fixes

• Empty results with correct-looking paths: inspect the source namespace URI and declare it in XMLNAMESPACES or the XQuery prolog.
• XML cast or parse error: check well-formedness, input encoding, parameter type, illegal characters, and whether the input contains one complete document.
• XMLTABLE conversion failure: find missing, repeated, oversized, or nonnumeric values and align PATH expressions with declared SQL types.
• Slow query: move document filtering to XMLEXISTS, narrow paths, reduce candidate relational rows, inspect XML index compatibility, update statistics, and review EXPLAIN.
• XMLMODIFY target error: make the updating path identify the required node cardinality and handle documents where that node is absent or duplicated.
• Truncated serialized output: increase the XMLSERIALIZE target size and verify the receiving host variable or application buffer.

Explain it like I'm 5

Think of an XML document as a toy box with smaller labeled boxes inside it. XMLPARSE checks that the toy box is assembled correctly and gives it to Db2. XMLQUERY opens the box and brings back one smaller box. XMLEXISTS answers, “Is there a red car anywhere at this exact place?” XMLTABLE takes every toy from one compartment and places it into neat spreadsheet rows. XMLSERIALIZE packs the box into text so another program can carry it away.

A namespace is like the maker's name printed beside a toy name: two toys both called “car” can be different when their makers differ. An XML index is a shortcut card telling Db2 where popular toys are. XMLMODIFY changes one toy without rebuilding the entire box, while schema validation checks that the box contains the kinds of toys the agreed instruction sheet requires.

Exercises

1. Create an XML document for an order with two lines. Insert it with XMLPARSE, retrieve it with XMLSERIALIZE, and explain why the serialized text is not the stored data type.
2. Write an XMLEXISTS predicate that accepts a SKU as an external variable. Test one SKU that exists and one that does not.
3. Use XMLTABLE to return line number, SKU, quantity, and price. Add FOR ORDINALITY and compare its value with any line number stored in the document.
4. Add a default namespace to the order document. Demonstrate the empty result from the old path, then fix it with a declared namespace.
5. Join XMLTABLE output to a relational product table. Change the join from inner to left outer and describe what happens to an unknown SKU.
6. Design an XML index for a selective customer or SKU path. State the pattern, namespace, SQL key type, and XMLEXISTS predicate expected to benefit.
7. Use XMLMODIFY in a test transaction to replace one status value. Test documents with zero, one, and two matching status nodes and document each outcome.
8. Build a negative-test checklist for malformed XML, schema-invalid XML, unsafe logging, oversized serialization, and failed numeric conversion.

Frequently asked questions