In OceanBase Database, XMLType data can only be stored in binary XML format.
XMLType columns have the following limitations:
- XMLType columns cannot be used as
PRIMARY KEY,FOREIGN KEY, orUNIQUE KEYcolumns, nor can they be used as partitioning keys. - XMLType columns do not support XMLIndex for key columns. You can create a generated column index based on an XMLType column, but the index type must be a base type. It cannot be
LOB,JSON,GIS, orXMLType.
The following example shows how to create a table that contains an XMLType column.
obclient> CREATE TABLE xml_test(c1 XMLType);
Query OK, 0 rows affected
obclient> DESC xml_test;
+-------+---------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+---------+------+-----+---------+-------+
| C1 | XMLTYPE | YES | NULL | NULL | NULL |
+-------+---------+------+-----+---------+-------+
1 row in set
In addition, OceanBase Database supports the following DDL operations on XMLType columns:
- Add and drop XMLType columns.
- Create a generated column index for an XMLType column.
The following example shows how to perform ALTER TABLE operations on an XMLType column. When you set a default value, you must use an expression or literal of the XMLType or VARCHAR2 data type.
obclient> CREATE TABLE alter_test(c1 NUMBER);
Query OK, 0 rows affected
obclient> ALTER TABLE alter_test ADD (c2 XMLType);
Query OK, 0 rows affected
obclient> ALTER TABLE alter_test MODIFY c2 NOT NULL;
Query OK, 0 rows affected
# Use an XMLType expression as the default value
obclient> ALTER TABLE alter_test MODIFY c2 DEFAULT '<a>abc</a>';
Query OK, 0 rows affected
obclient> ALTER TABLE alter_test DROP COLUMN c2;
Query OK, 0 rows affected
The following example shows how to define constraints on an XMLType column.
obclient> CREATE TABLE xml_test01(c1 XMLType, c2 NUMBER, c3 XMLType NOT NULL);
Query OK, 0 rows affected
obclient> ALTER TABLE xml_test01 ADD CONSTRAINT cst CHECK(c1 IS NOT NULL) ENABLE VALIDATE;
Query OK, 0 rows affected
The following example shows how to define a generated column based on an XMLType column.
obclient> CREATE TABLE xml_test02(c1 NUMBER, c2 XMLType,c3 NUMBER GENERATED ALWAYS AS(LENGTH(c2)));
Query OK, 0 rows affected
The following example shows how to create a new table based on a table that contains an XMLType column.
obclient> CREATE TABLE xml_test03(c1 NUMBER, c2 XMLType);
Query OK, 0 rows affected
obclient> INSERT INTO xml_test03 VALUES(11, '<a>abc</a>');
Query OK, 1 row affected
obclient> CREATE TABLE ctas1 AS SELECT * FROM xml_test03;
Query OK, 0 rows affected