OceanBase Database supports the following methods for querying XMLType data:
- Use the
SELECTstatement to access columns of the XMLType data type. - Use the
XMLSERIALIZE()function or theGETCLOBVAL()andGETSTRINGVAL()methods of XMLType. The data type of the returned XML text is defined by the function or method, such asVARCHAR2orCLOB. - Use the
EXTRACT(XML)function to extract a valid fragment from the XML text. The result type is XMLType. - Use the
EXTRACTVALUE()function to extract a valid fragment from the XML text. The return value type is currently only supported asVARCHAR2.
Additionally, OceanBase Database has the following rules for DML operations on XMLType data:
- Valid XML data of the
VARCHAR,VARCHAR2, andCHARdata types can be directly inserted into an XMLType column. - If the result of the
XMLPARSE()function is a validDOCUMENT, it can be directly inserted into an XMLType column. - If the result of the
XMLPARSE()function isCONTENT, it cannot be inserted into an XMLType column, regardless of whether its content can be considered a valid XMLDOCUMENT(i.e., it conforms to the syntax of XMLType 1.0 and has a unique root element). - If the result of the XMLType constructor is valid, it can be directly inserted into an XMLType column.
The following examples demonstrate how to query XMLType data and perform related DML operations.
# Execute an INSERT operation
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
# Directly use the SELECT statement to query
obclient> SELECT c1 FROM tt;
+-------------+
| C1 |
+-------------+
| <a>abc</a>
|
| <b>abc</b>
|
+-------------+
2 rows in set
# Use XML functions or PL methods to query. When accessing columns in this way, you must use 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
# Use the XMLSERIALIZE() function to query
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: