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

OceanBase Database allows you to query XMLType data in the following ways:

Use the SELECT statement to access an XMLType column.
Use the XML function XMLSERIALIZE() or the GETCLOBVAL() or GETSTRINGVAL() 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 the VARCHAR2 or CLOB type 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 the VARCHAR2 type.

In OceanBase Database, the following rules apply to DML operations on XMLType data:

You can insert XML data of the VARCHAR, VARCHAR2, and CHAR types 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 CONTENT is specified for the XMLPARSE() function, you cannot insert the result of the XMLPARSE() 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:

Customer Stories

Documentation

Query XMLType data

References