Adding XML columns

You can add XML columns to regular relational tables by using the ALTER TABLE statement.

About this task

When you add an XML column to a table, an XML table and XML table space are implicitly created to store the XML data. If the new XML column is the first XML column that you created for the table, Db2 also implicitly creates a BIGINT DOCID column to store a unique document identifier for the XML columns of a row.

Db2 also implicitly creates indexes. If this is the first XML column that you created for the table, Db2 implicitly creates an index on the DOCID column of the base table.

An XML column has several restrictions. The column cannot:

  • Be specified as part of the primary key
  • Be specified as part of a UNIQUE key
  • Have a DEFAULT value specified
  • Be specified as part of the FOREIGN KEY references clause
  • Be specified as part of the REFERENCES table-name clause
  • Be specified in the PARTITION BY RANGE clause
  • Be used in a materialized query table even if the table is specified WITH NO DATA
  • Be referenced in CHECK constraints
  • Have GENERATED specified
  • Have a FIELDPROC specified
  • Have AS SECURITY LABEL specified
  • Be added to a created temporary table
  • The table that contains an XML column will not have its XML column value passed to a VALIDPROC
  • Be part of a transition table

Procedure

Begin general-use programming interface information.To add an XML column to an existing table:

Issue the ALTER TABLE statement and specify the ADD column-name XML option.

Example

ALTER TABLE orders ADD shipping_info XML;
End general-use programming interface information.