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 ;