XML schema and validation in DB2

What an XML schema is

An XML schema (W3C XML Schema, XSD) describes which elements and attributes are allowed, their types (string, decimal, date), occurrence counts, and namespaces. One “schema” can be several schema documents that import or include each other—for example a common header.xsd plus order.xsd.

Db2 validation asks: given this instance document, does it conform to a registered schema? That is more than well-formedness (XMLPARSE) and different from business rules you write in SQL. Schema types also affect how values are typed inside XQuery after validation.

• Well-formed — tags nest, names are legal, there is a root; required for the XML type
• Valid — additionally matches a schema: required elements present, types legal, namespaces correct
• Invalid — well-formed but fails schema; INSERT/UPDATE with validation rejects the document

Schema documents you register must be in the Unicode encoding scheme. Plan your build pipeline so XSDs are UTF-8 before they hit XSR_REGISTER.

XML schema repository (XSR)

The XSR is Db2’s catalog for XML schemas used to validate (and historically to annotate) documents in XML columns. IBM creates it during install or migration. The sample DDL lives in SDSNSAMP (DSNTESR-style members processed by DSNTIJRT).

Objects include database DSNXSR, table spaces such as SYSXSR, and tables including:

• SYSIBM.XSROBJECTS — registered schema objects
• SYSIBM.XSRCOMPONENT / XSROBJECTCOMPONENTS — schema documents that make up a schema
• SYSIBM.XSROBJECTGRAMMAR / XSROBJECTHIERARCHIES — compiled grammar and include/import hierarchy
• SYSIBM.XSROBJECTPROPERTY / XSRPROPERTY — optional properties

The SQL schema name for user-registered XML schemas on z/OS is SYSXSR (or null, which defaults to SYSXSR). That is not your application schema HR or PAYROLL. The name part (for example POSCHEMA) is an SQL identifier; unquoted names fold to uppercase.

Important operational rule: do not drop or recreate XSR objects after you start validating. IBM warns that doing so can cause unexpected behavior. Treat XSR like catalog: back it up with the rest of the subsystem, and change contents through the supplied procedures.

If you never validate, you can skip XSR setup. If you plan DSN_XMLVALIDATE or type modifiers, complete the installation steps for XML schema support (stored procedures, WLM environment, JDBC/SQLJ driver pieces the procedures need).

XML schema registration

Registration is a conversation with three (or four) stored procedures, not a CREATE SCHEMA statement. Schema documents are passed as BLOB values.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 1. Primary document (rschema must be SYSXSR or null)
CALL SYSPROC.XSR_REGISTER(
  'SYSXSR',          -- rschema
  'POSCHEMA',        -- name -> SYSXSR.POSCHEMA
  :schema_location,  -- optional schema location URI, or null
  :xsd_blob,         -- primary XSD as BLOB
  :properties_blob   -- optional properties, or null
);
 
-- 2. Additional documents in the same schema (if any)
CALL SYSPROC.XSR_ADDSCHEMADOC(
  'SYSXSR',
  'POSCHEMA',
  :doc_location,
  :xsd_blob2,
  :properties_blob
);
 
-- 3. Complete — for-decomposition must be 0 on z/OS
CALL SYSPROC.XSR_COMPLETE(
  'SYSXSR',
  'POSCHEMA',
  :properties_blob,
  0
);
 
COMMIT;

XSR_COMPLETE folds undelimited names to uppercase. Pass '"POschema"' if you need mixed case. The for-decomposition argument must be 0: annotated schema decomposition is not supported on z/OS the way LUW supports DECOMPOSE XML DOCUMENT.

Until COMPLETE succeeds, the schema is not ready for validation. A failure in the middle leaves a partial registration—clean up with XSR_REMOVE and retry rather than guessing at catalog rows.

XML schema repository administration

Day-to-day XSR work is procedure calls plus SELECT on the SYSIBM.XSR* tables (with the usual catalog privileges). Administration tasks:

• Inventory — query XSROBJECTS for names, namespaces, timestamps
• Register — change-control the XSD source; run REGISTER / ADD / COMPLETE in a controlled job
• Remove — XSR_REMOVE when a version is retired and no type modifier still requires it
• Backup — include DSNXSR in subsystem backup/recovery; losing XSR breaks validation even if XML data remains
• Authorization — EXECUTE on the XSR procedures is not granted to public in a locked-down shop; treat registration as a DBA task

Do not UPDATE XSR tables with ordinary SQL to “fix” a schema. The compiled grammar in the repository must stay consistent with the documents. Always go through the procedures.

XML schema deletion

SYSPROC.XSR_REMOVE deletes a registered XML schema from the XSR. Call it with the same rschema and name you registered.

1
2
CALL SYSPROC.XSR_REMOVE('SYSXSR', 'POSCHEMA');
COMMIT;

Remove type-modifier references first (ALTER the XML column) if the column still names that schema. Removing a schema that a modifier still lists leaves you with a column definition pointing at a missing XSR object—validation then fails for new writes. Plan the order: ALTER modifier → drain in-flight work → XSR_REMOVE.

Deletion does not rewrite existing XML values. Rows already stored stay as they are. Only future validation calls need the schema to still exist.

XML validation and XMLVALIDATE / DSN_XMLVALIDATE

XML schema validation is determining whether structure, content, and data types of a document are valid according to a schema. On z/OS you do it in two supported ways:

DSN_XMLVALIDATE (explicit)

DSN_XMLVALIDATE returns an XML value. If validation fails, the statement errors. Forms include:

• DSN_XMLVALIDATE(string-expression) — validate a textual document; schema chosen by hints in the instance (xsi:schemaLocation) against the XSR
• DSN_XMLVALIDATE(xml-expression) — same idea for an XML value
• DSN_XMLVALIDATE(xml-or-string, varchar-name) — second argument is the qualified XSR name, for example 'SYSXSR.POSCHEMA'
• Three-argument forms — identify the schema by namespace / location pieces when you are not using the two-part SQL name

1
2
3
4
5
INSERT INTO PURCHASE_ORDER (PO_ID, CONTENT)
VALUES (
  1001,
  DSN_XMLVALIDATE(:xml_hv, 'SYSXSR.POSCHEMA')
);

There have been two implementations: an older user-defined SYSFUN.DSN_XMLVALIDATE and the built-in SYSIBM.DSN_XMLVALIDATE. New code should use the built-in. IBM documents a migration path if you still call the UDF.

On LUW you would write XMLVALIDATE(XMLPARSE(...) ACCORDING TO XMLSCHEMA ID ...). Do not paste that into z/OS COBOL unchanged. Conceptually it is the same step: parse or supply XML, validate, store.

XML type modifier (implicit)

An XML type modifier attaches one or more schemas to the column. Every INSERT or UPDATE of that column validates against a schema in the list. You do not have to remember DSN_XMLVALIDATE in every program—which is the point for shops with many writers.

1
2
3
4
5
6
7
8
9
10
CREATE TABLE PURCHASE_ORDER (
  PO_ID    INTEGER NOT NULL,
  CONTENT  XML (XMLSCHEMA ID SYSXSR.POSCHEMA),
  PRIMARY KEY (PO_ID)
);
 
-- Later: stop automatic validation
ALTER TABLE PURCHASE_ORDER
  ALTER CONTENT
  SET DATA TYPE XML;

You can identify schemas by ID (SYSXSR.name) or by URI / target namespace so that whatever schemas exist in that namespace at CREATE/ALTER time are associated. Not every schema the modifier might eventually need must already be registered at CREATE, depending on how you specify the modifier—read the URI vs ID rules before assuming a later REGISTER automatically attaches.

If the column has no modifier, validation is optional: store well-formed XML, or call DSN_XMLVALIDATE when a particular document must be checked.

XML schema versioning and evolution

Real documents change. A 2024 order schema adds a new optional element; a 2026 schema makes it required. Db2 does not magically rewrite old XML. You version schemas as separate XSR objects and evolve the type modifier.

Versioning pattern

• Register PO1 and PO2 as SYSXSR.PO1 and SYSXSR.PO2 (or names that include a version token)
• Widen the modifier so the column accepts either schema during a dual-write period
• Migrate old documents with a batch that XMLQUERY/XMLTABLE reads, constructs new XML, and UPDATEs (validating as PO2)
• Narrow the modifier to PO2 only, then XSR_REMOVE PO1 when nothing references it

1
2
3
4
5
6
7
8
9
10
11
12
-- Accept both versions
ALTER TABLE PURCHASE_ORDER
  ALTER CONTENT
  SET DATA TYPE XML (
    XMLSCHEMA ID SYSXSR.PO1,
    ID SYSXSR.PO2
  );
 
-- After migration, only PO2
ALTER TABLE PURCHASE_ORDER
  ALTER CONTENT
  SET DATA TYPE XML (XMLSCHEMA ID SYSXSR.PO2);

Evolution is this operational process—not an in-place ALTER of an XSD already stored in XSR. To “change” a schema, register a new complete schema and switch references. Trying to overwrite XSR rows in place is unsupported.

Optional elements are easier to evolve than required ones. If producers cannot guarantee the new field, keep it optional in PO2 or you will reject old-shaped documents the moment you drop PO1 from the modifier.

ALTER of the type modifier does not re-validate existing rows. A column can contain documents that would fail the new modifier until you rewrite them. Schedule the data migration; do not assume ALTER TABLE is a data-quality tool.

Choosing a validation strategy

Beginners turn on a type modifier for every XML column and then wonder why a test payload fails. Veterans pick per column:

• System of record documents — type modifier; reject garbage at the door
• Opaque archive — well-formed only; maybe validate in a later pipeline
• Multiple feed formats — several schemas on one modifier, or DSN_XMLVALIDATE with a schema name chosen from a relational TYPE column
• Development — no modifier, validate in unit tests, add the modifier before production

Validation costs CPU. Huge documents against complex XSDs are not free. Measure. XML indexes and query still work on unvalidated XML; validation is about integrity, not a prerequisite for XMLQUERY.

Explain It Like I'm Five

Well-formed XML is a Lego castle that does not fall apart. A schema is the picture on the box: “this castle must have a gate and four towers.” The XSR is the library shelf where the librarian keeps those box pictures. Register means putting a new picture on the shelf; remove means taking it down. A type modifier is a sign on the toy bin: “only castles that match these pictures allowed.” DSN_XMLVALIDATE is asking the librarian to check one castle against a picture before you put it in a bin that does not have a sign. When the toy company changes the set, you put a new picture on the shelf (version 2) and eventually stop accepting version 1.

Exercises

1. List the three stored procedures used to register a multi-document XML schema, in order, and what the for-decomposition argument must be on z/OS.
2. Write CREATE TABLE for ORDERS with ORDER_ID INTEGER and DOC XML that must validate against SYSXSR.ORDERV1.
3. Write an INSERT that uses DSN_XMLVALIDATE with a host variable and schema name SYSXSR.ORDERV1 (no type modifier).
4. Describe the ALTER steps to accept both ORDERV1 and ORDERV2, then to drop ORDERV1 from the modifier and from the XSR.
5. Why must XSD documents be Unicode when registered, and why should you not DROP the XSR tables after go-live?