Query XMLType data|V4.3.5|OceanBase Database| docs|Distributed Database

Query XMLType data

Last Updated：2026-04-09 02:53:55 Updated

OceanBase Database supports the following methods for querying XMLType data:

Use the SELECT statement to access columns of the XMLType data type.
Use the XMLSERIALIZE() function or the GETCLOBVAL() and GETSTRINGVAL() methods of XMLType. The data type of the returned XML text is defined by the function or method, such as VARCHAR2 or CLOB.
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 as VARCHAR2.

Additionally, OceanBase Database has the following rules for DML operations on XMLType data:

Valid XML data of the VARCHAR, VARCHAR2, and CHAR data types can be directly inserted into an XMLType column.
If the result of the XMLPARSE() function is a valid DOCUMENT, it can be directly inserted into an XMLType column.
If the result of the XMLPARSE() function is CONTENT, it cannot be inserted into an XMLType column, regardless of whether its content can be considered a valid XML DOCUMENT (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:

OceanBase

Customer Stories

Documentation

Query XMLType data

References