OceanBase Database allows you to query XMLType data in the following ways:
- Use the
SELECTstatement to access columns of the XMLType data type. - 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, such asVARCHAR2orCLOB. - Use the
EXTRACT(XML)function to extract a valid fragment from the XML text. The result is of the XMLType data type. - Use the
EXTRACTVALUE()function to extract a valid fragment from the XML text. Currently, only theVARCHAR2return value type is supported.
In addition, OceanBase Database has the following rules for DML operations on the XMLType data type:
- You can directly insert valid XML data of the
VARCHAR,VARCHAR2, orCHARdata type into an XMLType column. - If the result of the
XMLPARSE()function is a validDOCUMENT, you can directly insert it into an XMLType column. - If the result of the
XMLPARSE()function isCONTENT, you cannot insert it into an XMLType column, regardless of whether its content can be considered a valid XMLDOCUMENT(i.e., whether it conforms to the syntax of XMLType 1.0 and has a unique root element). - If the result of the XMLType constructor is valid, you can directly insert it into an XMLType column.
Here are some examples of querying XMLType data and performing 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
obclient> SELECT c1 FROM tt;
+-------------+
| C1 |
+-------------+
| <a>abc</a>
|
| <b>abc</b>
|
+-------------+
2 rows in set
# Use an XML function or PL method to query. When accessing a column by using this method, 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);
ORA-01427: single-row subquery returns more than one row
References
For more information about XML functions and PL methods, see the following topics: