In OceanBase Database, XMLType data is stored only as binary XML data.
Limitations on XMLType columns are as follows:
- An XMLType column cannot be used as a primary key, foreign key, unique key, or partitioning key.
- An XMLType column cannot be used as the key column of an
XMLindexindex. However, you can create a generated column index based on an XMLType column, with the index type being a basic one rather thanLOB,JSON,GIS, orXMLType.
The following example creates 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 ALTER TABLE operations on an XMLType column. To set a default value, you must use an expression or literal that returns XMLType data or VARCHAR2 data.
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 defines a constraint 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 generates a 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 creates 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