Addition of XML columns to existing tables

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

Example The sample database contains a table for 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: Setting the COMPRESS attribute to YES enables data row compression. XML documents stored in XML columns are subject to row compression. Compressing data at the row level allows repeating patterns to be replaced with shorter symbol strings.

ALTER TABLE MyCustomer ADD COLUMN Preferences XML COMPRESS YES;

Example: The following CREATE TABLE statement creates a patient table partitioned by visit date. All records between January 01, 2000 and December 31, 2006 are in the first partition. The more recent data are partitioned every 6 months.

CREATE TABLE Patients ( patientID INT, Name Varchar(20), visit_date DATE, 
      diagInfo XML )
    PARTITION BY ( visit_date )
       ( STARTING '1/1/2000' ENDING '12/31/2006',
         STARTING '1/1/2007' ENDING '6/30/2007',
            ENDING '12/31/2007',
            ENDING '6/30/2008',
            ENDING '12/31/2008', 
            ENDING '6/30/2009' );

The following ALTER table statement adds another XML column for patient prescription information:

ALTER TABLE Patients ADD COLUMN prescription XML ;