OceanBase Database supports the following methods for querying XMLType data:
- Use the
SELECTstatement to access columns of the XMLType data type. - Access XMLType data by using 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. - Access a valid fragment of XML text by using the
EXTRACT(XML)function. In this case, the result is of the XMLType data type. - Access a valid fragment of XML text by using the
EXTRACTVALUE()function. In this case, the result is of theVARCHAR2data type.
OceanBase Database also supports the following 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, regardless of whether the content can be considered a valid XMLDOCUMENT(that is, whether it conforms to the syntax of XMLType 1.0 and has a unique root element). - You can directly insert the result of an 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.
# Perform 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 data.
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 access a column, 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
# 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: