OceanBase Database allows you to query XMLType data in the following ways:
- Use the
SELECTstatement to access an XMLType column. - Use the XML function
XMLSERIALIZE()or theGETCLOBVAL()orGETSTRINGVAL()method of XMLType to access XMLType data. In this case, the data type of the returned XML text is defined by the function or method. For example, data of theVARCHAR2orCLOBtype may be returned. - Use the
EXTRACT(XML)function to obtain a valid XML fragment. The function returns data of the XMLType type. - Use the
EXTRACTVALUE()function to obtain a valid XML fragment. Currently, the function can only return data of theVARCHAR2type.
In OceanBase Database, the following rules apply to DML operations on XMLType data:
- You can insert XML data of the
VARCHAR,VARCHAR2, andCHARtypes into an XMLType column. - You can insert the result of the
XMLPARSE()function into an XMLType column if the result is a valid XML document. - If
CONTENTis specified for theXMLPARSE()function, you cannot insert the result of theXMLPARSE()function into an XMLType column no matter whether the content can be considered a valid XML document. XML content is considered a valid XML document if it conforms to the XMLType 1.0 syntax and has a unique root element. - You can 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 related DML operations.
# Perform INSERT operations.
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
# Use SELECT to query data.
obclient> SELECT c1 FROM tt;
+-------------+
| C1 |
+-------------+
| <a>abc</a>
|
| <b>abc</b>
|
+-------------+
2 rows in set
# Use an XML function or a PL method to query data. When you access an XMLType column 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 data.
obclient> SELECT XMLSERIALIZE(DOCUMENT c1) FROM tt;
+--------------------------+
| XMLSERIALIZE(DOCUMENTC1) |
+--------------------------+
| <a>abc</a>
|
| <b>abc</b>
|
+--------------------------+
2 rows in set
# Perform INSERT + SELECT operations.
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
# Perform UPDATE operations.
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: