XML schema validation with an XML type modifier

You can automate XML schema validation by adding an XML type modifier to an XML column definition.

An XML type modifier associates a set of one or more XML schemas with the XML data type. This type modifier enforces that all XML documents that are stored in an XML column are validated according to one of the XML schemas that are specified in the type modifier. Before schema validation through an XML type modifier can occur, all schema documents that make up an XML schema must be registered in the built-in XML schema repository (XSR).

Alternatively, you might want to associate a single XML schema with multiple type modifiers. An XML schema can define many different documents. You might need to separate the XML documents into different columns, but specify the same XML schema in a type modifier for each column.

For example, a sales department might have one XML schema that defines purchase orders and billing statements. You can store purchase orders in one XML column, and billing statements in another XML column. Both XML columns have an XML type modifier that points to the same XML schema, but each column uses a different document in the XML schema.

Not all XML schemas that the XML type modifier identifies need to be registered before you execute the CREATE or ALTER statement. If the XML type modifier specifies a target namespace, only the XML schemas in that target namespace that exist when the CREATE or ALTER statement is executed are associated with the XML type modifier.

The following examples of defining an XML type modifier refer to these XML schemas.

Example: Use the following SQL statement to create a table named PURCHASEORDERV1, with an XML type modifier on the CONTENT column. The XML type modifier uses the URI and LOCATION keywords to uniquely identify XML schema PO2.

CREATE TABLE PURCHASEORDERV1(
 ID INT NOT NULL,
 CONTENT XML(XMLSCHEMA URI 'http://www.example.com/PO2'
 LOCATION 'http://www.example.com/PO2.xsd'))

Example: Use the following SQL statement to create a table named PURCHASEORDERV2, with an XML type modifier on the CONTENT column. The XML type modifier uses the URI keyword to identify the XML schemas. If you execute the CREATE statement before 2009-10-25 13:15:00.0200, the XML type modifier identifies only XML schema PO2, and PO2 is used to validate any INSERT operations that are performed. Db2 does not add PO4 to the XML type modifier after PO4 is registered. If you execute the CREATE statement after 2009-10-25 13:15:00.0200, an SQL error occurs because the XML type modifier uses the URI keyword to identify two XML schemas: PO2 and PO4. The URI keyword must identify only one XML schema.

CREATE TABLE PURCHASEORDERV2(
 ID INT NOT NULL,
 CONTENT XML(XMLSCHEMA URI 'http://www.example.com/PO2'))

Example: Use the following SQL statement to create a table named PURCHASEORDERV3, with an XML type modifier on the CONTENT column that uses XML schema PO3. XML schema PO3 has no namespace, so you need to use the NO NAMESPACE keyword in the XML type modifier.

CREATE TABLE PURCHASEORDERV3(
 ID INT NOT NULL,
 CONTENT XML(XMLSCHEMA NO NAMESPACE
 LOCATION 'http://www.example.com/PO3.xsd'))
)

Example: Suppose that XML schema PO1 has two global elements: purchaseOrder and comment. Use the following SQL statement to create a table named PURCHASEORDERV4, with an XML type modifier on the CONTENT column that causes validation to be performed against the purchaseOrder element in XML schema PO1.

CREATE TABLE PURCHASEORDERV4(
 ID INT NOT NULL,
 CONTENT XML(XMLSCHEMA ID SYSXSR.PO1
 ELEMENT "purchaseOrder"))

Example: Use the following SQL statement to create a table named PURCHASEORDERV5, with an XML type modifier on the CONTENT column that includes multiple XML schemas: PO1, PO2, PO3, and PO4.

CREATE TABLE PURCHASEORDERV5(
 ID INT NOT NULL,
 CONTENT XML(XMLSCHEMA ID SYSXSR.PO1, ID SYSXSR.PO2,
 ID SYSXSR.PO3, ID SYSXSR.PO4))

Example: Suppose that new documents that will be added to table PURCHASEORDERV1 will have a new format. They need to conform to XML schema PO1. Alter the XML column to add PO1 to the XML type modifier:

ALTER TABLE PURCHASEORDERV1(
 ALTER CONTENT
 SET DATA TYPE XML(XMLSCHEMA
  ID SYSXSR.PO1,
  ID SYSXSR.PO2))

The table space that contains the XML documents for the CONTENT column is not put in CHECK-pending status, because all existing documents conform to XML schema SYSXSR.PO2.

Example: Suppose that you no longer want documents in the CONTENT column to conform to XML schema SYSXSR.PO2. Alter the XML column to remove SYSXSR.PO2 to the XML type modifier:

ALTER TABLE PURCHASEORDERV1
  ALTER CONTENT 
  SET DATA TYPE XML(XMLSCHEMA
  ID SYSXSR.PO1))

The table space that contains the XML documents for the CONTENT column is put in CHECK-pending status, because all existing documents now need to conform only to XML schema SYSXSR.PO1.

Example: Suppose that you no longer need to do automatic validation of documents in the CONTENT column. Alter the column to remove the XML type modifier:

ALTER TABLE PURCHASEORDERV1
  ALTER CONTENT 
  SET DATA TYPE XML

The table space that contains the XML documents for the CONTENT column is not put in CHECK-pending status, because there is no longer an XML schema to which the existing documents need to conform.