OceanBase Database allows you to query XMLType data in the following ways:
- Use the
SELECTstatement to query XMLType data. - Use the
XMLSERIALIZE()function or theGETCLOBVAL()andGETSTRINGVAL()methods of the XMLType data type. The data type of the returned XML text is defined by the function or method, for example,VARCHAR2orCLOB. - Use the
EXTRACT(XML)function to extract a valid fragment from the XML text. The returned result is of the XMLType data type. - Use the
EXTRACTVALUE()function to extract a valid fragment from the XML text. Currently, only theVARCHAR2data type is supported.
OceanBase Database has the following rules for DML operations on XMLType data:
- You can directly insert valid XML data of the
VARCHAR,VARCHAR2, orCHARdata type into an XMLType column. - You can directly insert the result of the
XMLPARSE()function into an XMLType column if the result is a validDOCUMENT. - You cannot insert the result of the
XMLPARSE()function into an XMLType column if the result isCONTENT, even if the content can be considered a valid XMLDOCUMENT(that is, it conforms to the syntax of XMLType 1.0 and has a unique root element). - You can directly insert the result of the XMLType constructor into an XMLType column if the result is valid.
The following examples show how to query XMLType data and perform DML operations on it.
# Execute the INSERT statement
obclient> CREATE TABLE tt(c1 XMLType, c2 XMLType);
Query OK, 0 rows affected
obclient> INSERT INTO tt VALUES('<a>abc</a>', '<a>ccc</a>');
Query OK, 1 row affected
obclient> INSERT INTO tt VALUES(XMLType('<b>abc</b>'), XMLPARSE(DOCUMENT '<b>ccc</b>'));
Query OK, 1 row affected
# Query data directly by using the SELECT statement
obclient> SELECT c1 FROM tt;
+-------------+
| C1 |
+-------------+
| <a>abc</a>
|
| <b>abc</b>
|
+-------------+
2 rows in set
# Query data by using an XML function or a PL method. When you use this method to query data, you must specify a table alias.
obclient> SELECT a.c1.GETCLOBVAL() FROM tt a;
+-------------------+
| A.C1.GETCLOBVAL() |
+-------------------+
| <a>abc</a>
|
| <b>abc</b>
|
+-------------------+
2 rows in set
obclient> SELECT a.c1.GETSTRINGVAL() FROM tt a;
+---------------------+
| A.C1.GETSTRINGVAL() |
+---------------------+
| <a>abc</a>
|
| <b>abc</b>
|
+---------------------+
2 rows in set
# Query data by using the XMLSERIALIZE() function
obclient> SELECT XMLSERIALIZE(DOCUMENT c1) FROM tt;
+--------------------------+
| XMLSERIALIZE(DOCUMENTC1) |
+--------------------------+
| <a>abc</a>
|
| <b>abc</b>
|
+--------------------------+
2 rows in set
# INSERT + SELECT scenario
obclient> CREATE TABLE test_xml1(
c1 NUMBER, c2 BINARY_FLOAT, c3 BINARY_DOUBLE,
c4 DATE, c5 VARCHAR(100), c6 CHAR(100),
c7 CLOB, c8 BLOB, c9 TIMESTAMP, c10 TIMESTAMP WITH TIME ZONE,
c11 TIMESTAMP WITH LOCAL TIME ZONE, c12 RAW(100),
c13 INTERVAL YEAR TO MONTH,
c14 INTERVAL DAY TO SECOND, c15 NVARCHAR2(100),
c16 NCHAR(100), c17 VARCHAR2(20), c18 XMLType);
Query OK, 0 rows affected
obclient> CREATE TABLE test_xml2(c1 INT, c2 XMLType);
Query OK, 0 rows affected
obclient> INSERT INTO test_xml2 SELECT 5, c5 FROM test_xml1;
Query OK, 0 rows affected
# UPDATE scenario
obclient> CREATE TABLE test_xml3(c1 NUMBER, c2 XMLType);
Query OK, 0 rows affected
obclient> UPDATE test_xml3 SET c2='<a>abc</a>';
Query OK, 0 rows affected
Rows matched: 0 Changed: 0 Warnings: 0
obclient> UPDATE test_xml3 SET c2=XMLPARSE(DOCUMENT '<a>ccc</a>');
Query OK, 0 rows affected
obclient> UPDATE test_xml3 SET c2=(SELECT c2 FROM tt);
OBE-01427: single-row subquery returns more than one row
References
For more information about XML functions and PL methods, see the following topics: