Altering tables with XML columns

To add XML columns to existing tables, you specify columns with the XML data type in the ALTER TABLE statement with the ADD COLUMN clause. A table can have one or more XML columns.

To alter an existing XML column to include an XML type modifier or remove an XML type modifier, use ALTER TABLE.

When you add XML columns to a table, the Db2 database server implicitly creates a table space and table for each XML column. The data for an XML column is stored in the corresponding table.

An XML type modifier associates a set of one or more XML schemas with the XML data type. You can use an XML type modifier to cause all XML documents that are stored in an XML column to be validated according to one of the XML schemas that is specified in the type modifier. When you perform either of the following actions, Db2 puts the XML table space that corresponds to the altered XML column in CHECK-pending status:

Add an XML type modifier to an existing XML column that does not have a type modifier but contains XML data
Remove an XML schema from an existing XML type modifier that has more than one XML schema

When the XML table space is in CHECK-pending status, you need to run CHECK DATA to validate the values of the altered XML column for existing rows. If you add an XML schema to the type modifier of an existing XML column, Db2 does not put the XML table space in CHECK-pending status, and values of the altered column in the existing rows are not revalidated.

When you add an XML column to a table that is in a universal table space, Db2 maintains multiple versions of XML documents during update operations, to enhance concurrency and memory usage.

Example: A table contains customer data that contains two XML columns. The definition looks like this:

CREATE TABLE Customer (Cid BIGINT NOT NULL PRIMARY KEY,
                       Info XML,
                       History XML)

Create a table named MyCustomer that is a copy of Customer, and add an XML column to describe customer preferences:

CREATE TABLE MyCustomer LIKE Customer;
ALTER TABLE MyCustomer ADD COLUMN Preferences XML;

Example: A table for customer data contains an XML column named CONTENT. The definition looks like this:

CREATE TABLE PURCHASEORDERS(
  ID INT NOT NULL,
  CONTENT XML)

The table contains several XML documents. The documents in the XML column need to be validated according to XML schema SYSXSR.PO1, which has already been registered. Alter the XML column to add an XML type modifier that specifies SYSXSR.PO1:

ALTER TABLE PURCHASEORDERS
  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. You need to run CHECK DATA against the XML table space to remove the CHECK-pending status.