OceanBase v4.2特性解析：Oracle 的XML Type兼容

2024-04-24 18:11:12

概述：在OceanBase 的Oracle模式支持XMLType特性后，OceanBase拥有了XML的数据存储、计算、分析能力。用户无需将XML数据作为文本存储，在业务代码中解析并操作XML文本，而是可以直接基于XML内置的能力，对XML数据进行存储、构造、增删改查。

1. 背景

1.1. XML的能力与应用场景

在数据库的描述配置、数据交换等场景中，XML（Extensible Markup Language）的身影屡见不鲜。这种数据交换格式基于文本，具有自解释性且标准化。因为企业用户的数据资产往往储存在关系数据库中，以二维表的形式进行逻辑存储，故而在数据传输与存储交换过程中，通常会产生以下需求：

从关系数据库中查询的一个数据集合，转换为一个XML文档。
提取XML文档的内容，以关系表的方式存储进数据库。
直接在关系数据库中存储、处理XML文档。

同时，在此过程中，XML展现了它的五项能力。

能力1：数据组织结构。

XML将数据按照树形结构组织，通过这种嵌套结构，XML可以存储非常大的数据集。

能力2：约束。

XML提供两种约束方式，

直接在单个XML文档中定义的约束称为DTD
通过定义XSchema，来描述一系列XML文档的组织以及数据特征，其中Schema也是基于XML标准定义的一种特殊的XML文档。

能力3：查询。

最简单的Xpath类似于我们的文件路径，是访问树形结构最普遍的一种方式。XML标准赋予XPath更强大的能力，比如谓词过滤、基于函数的计算处理、复杂的查询等能力。

能力4：DML。

XML标准定义了一系列DOM(The W3C Document Object Model)接口，提供了一套通用、跨平台的方法用于Get/Change/Add/Delete XML

能力5：XQuery。

这是XML特有的一种操作语言，有点类似数据库的PL。其能力不仅仅是"Query"，也提供了对XML的DML操作，以及复杂的流程控制能力（比如循环、赋值、条件语句、跳转、排序、过滤等）。

1.2. OceanBase v4.2 XML Type特性支持

上述可见，XML涉及的能力非常多。它的标准也非常复杂。正因此，在OceanBase 4.2版本发布的Oracle XML兼容性特性无法做到非常齐全，暂只包括以下四个特性。

特性1：XMLType支持。

用户可以定义数据为XML类型，通过PL或SQL操作XML数据。

特性2：基础函数。

主要用于XML数据的构造/增删改查。

构造：通过构造函数，可以将关系数据库中的数据集合，转换为XML。比如XMLParse、XMLElement、XMLAttributes、XMLAgg等函数。
查询：查询函数支持基于Xpath的查询, 比如Extract。
更新：通过更新函数可以对XML文档做增量修改，改变某个XML节点的数据，比如UpdateXML。
格式转换：将数据库存储的XML数据格式转换为标准的XML文本，比如XMLSERIALIZE。

特性3：存储。

支持原生的XML Binary存储，这是一种查询友好的XML存储格式，相较直接基于文本存储，会避免XML文档的解析，也会加速XML的查询。

特性4：索引。

基于虚拟生成列可以在XML文档上建立索引。

部分XML标准以及Oracle XML的功能点不在OceanBase 4.2版本发布的范围内，比如：

XQuery
XMLDOM
XML Package

1.3. 基于数据库能力处理XML

在OceanBase 的Oracle模式支持XMLType特性后，OceanBase拥有了XML的数据存储、计算、分析能力。用户无需将XML数据作为文本存储，在业务代码中解析并操作XML文本，而是可以直接基于XML内置的能力，对XML数据进行存储、构造、增删改查。

基于数据库能力处理XML的优势在于三点。

第一，增量查询/更新。数据库精确返回、修改用户需要操作的XML子节点数据，避免客户端和数据库交换完整XML数据，降低网络开销。

第二，查询优化。基于文本处理XML，每次都需要对XML进行解析，有比较大的解析开销。基于XMLType存储的XML数据只需在入库的时候解析一次，后续查询均不用解析，而且存储格式对查询做了特殊优化。

第三，"想要即所得"。数据库内置了关系数据向XML的相互转换能力，只用简单的SQL即可基于数据库中的数据构造复杂的XML文档，客户端不用基于数据库数据做二次处理。

可以说，OceanBase 4.2版本基于XML基础能力的组合，可以满足绝大部分XML的数据处理场景，"麻雀虽小五脏俱全"。

2. 使用操作

2.1. 创建含有XmlType列的表

# 创建含有xmltype列的表
OceanBase(SYS@SYS)>create table xml_t(id number, c1 xmltype);
Query OK, 0 rows affected (0.522 sec)

OceanBase(SYS@SYS)>desc xml_t;
+-------+---------+------+-----+---------+-------+
| FIELD | TYPE    | NULL | KEY | DEFAULT | EXTRA |
+-------+---------+------+-----+---------+-------+
| ID    | NUMBER  | YES  | NULL | NULL    | NULL  |
| C1    | XMLTYPE | YES  | NULL | NULL    | NULL  |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.079 sec)

2.2. XmlType的DML操作

#写入数据，可以直接使用合法的xml文本
OceanBase(SYS@SYS)>insert into xml_t values(1, '<?xml version="1.0" encoding="UTF-8" ?>
    '> <employee id="1">
    '> <name>Alice</name>
    '> <age>25</age>
		'> <empdate>2019-03-14</empdate>
    '> </employee>');
Query OK, 1 row affected (0.017 sec)

# 或是借助xmlparse表达式显式的将文本解析成xmltype数据后插入xmltype列
OceanBase(SYS@SYS)>insert into xml_t values(2, xmlparse(document '<?xml version="1.0" encoding="UTF-8" ?>
    '> <employee id="2">
    '> <name>Bob</name>
    '> <age>30</age>
    '> <empdate>2010-01-01</empdate>
    '> </employee>'));
Query OK, 1 row affected (0.006 sec)

#更新xmltype数据的方式与其它数据类型类似，同样使用update语句，例如将Bob的入职日期改为2010年2月1日
OceanBase(SYS@SYS)>update xml_t set c1=xmlparse(document '<?xml version="1.0" encoding="UTF-8" ?>
    '> <employee id="2">
    '>   <name>Bob</name>
    '>   <age>30</age>
    '>   <empdate>2010-02-01</empdate>
    '> </employee>') where id = 2;
Query OK, 1 row affected (0.010 sec)
Rows matched: 1  Changed: 1  Warnings: 02;

2.3. 查询XmlType数据

# 普通查询
OceanBase(SYS@SYS)>select c1 from xml_t;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| C1                                                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| <?xml version="1.0" encoding="UTF-8"?>
<employee id="1">
  <name>Alice</name>
  <age>25</age>
  <empdate>2019-03-14</empdate>
</employee>
 |
| <?xml version="1.0" encoding="UTF-8"?>
<employee id="2">
  <name>Bob</name>
  <age>30</age>
  <empdate>2010-02-01</empdate>
</employee>
   |
+--------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.005 sec)

# 也可通过pl方法，getclobval() 和getstringval() 进行查询，查询到的内容不会被改变，
# 但返回类型变为CLOB或者VARCHAR2，注意使用此种方法时，必须使用表别名

select t.c1.getclobval() from xml_t t;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| T.C1.GETCLOBVAL()                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| <?xml version="1.0" encoding="UTF-8"?>
<employee id="1">
  <name>Alice</name>
  <age>25</age>
  <empdate>2019-03-14</empdate>
</employee>
 |
| <?xml version="1.0" encoding="UTF-8"?>
<employee id="2">
  <name>Bob</name>
  <age>30</age>
  <empdate>2010-02-01</empdate>
</employee>
   |
+--------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.260 sec)

2.4. 表达式

2.4.1. 本次特性支持的XML表达式总览

表达式名称	功能简述
XMLPARSE	解析字符串输入，如果是合法，则将其转换为xmltype数据
XMLELEMENT	依据输入参数构造一个xmlelement，返回xmltype数据
XMLATTRIBUTES	依据输入参数构造xmlattributes，只能作为xmlement表达式的输入，不能单独使用
XMLAGG	聚合函数，将多个XML片段聚合成一个xmltype数据
EXTRACT	依据Xpath抽取一个XML片段，其结果类型是XMLTYPE
EXTRACTVALUE	依据Xpath抽取一个XML片段，默认返回类型为VARCHAR2(4000)
XMLSERIALIZE	将XMLType数据序列化为varchar2或者clob，可通过参数进行格式化
XMLCAST	抽取XML文档的内容（不包括element name），并将其转换为用户指定的内容
UPDATEXML	替换XMLType文档中，Xpath指定的部分内容

EXTRACT，EXTRACTVALUE，UPDATEXML 需要指定Xpath，Xpath用来访问XML数据中心，特定的元素或属性。OB当前支持了XPath 1.0的大部分的location path能力，和一部分filter，function能力。

本次发布尚未支持的常用表达式有：

表达式名称	功能简述
XMLTABLE	将XML文档展开成一张关系表
XMLEXIST	用来判断某个Xpath指定的路径在XML数据中是否存在
XMLISVALID	校验XML文档是否符合XMLSchema的定义
XMLQUERY	用来执行xquery表达式

2.4.2. 构造XML数据的表达式

2.4.2.1. XMLPARSE

XMLPARSE 表达式用于将一个字符串进行解析，如果是合法的XML文本，则将其转换为XMLType数据并返回。

# 解析document
OceanBase(SYS@SYS)>SELECT XMLPARSE(DOCUMENT '<?xml version="1.0" encoding="UTF-8" ?>
    '> <employee id="1">
    '>     <name>Alice</name>
    '>     <age>25</age>
    '>     <empdate>2019-03-14</empdate>
    '> </employee>') AS PO FROM DUAL;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| PO                                                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| <?xml version="1.0" encoding="UTF-8"?>
<employee id="1">
  <name>Alice</name>
  <age>25</age>
  <empdate>2019-03-14</empdate>
</employee>
 |
+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.004 sec)

2.4.2.2. XMLELEMENT

XMLELEMENT 表达式用于构造一个XMLELEMENT，可以指定ELEMENT名称，属性以及内容，此方法用于将关系表数据转换成XML类型。

2.4.2.3. XMLATTRIBUTES

XMLATTRIBUTES 表达式用于构造XML 属性，只能作为XMLELEMENT表达式的输入。

OceanBase(SYS@SYS)>create table employees(name varchar2(20), empdate varchar2(20));
Query OK, 0 rows affected (0.161 sec)

OceanBase(SYS@SYS)>insert into employees values('Tom', '2020-01-01');
Query OK, 1 row affected (0.027 sec)

OceanBase(SYS@SYS)>insert into employees values('Jerry', '2020-02-01');
Query OK, 1 row affected (0.002 sec)

OceanBase(SYS@SYS)>select xmlelement(emp, xmlattributes(name), empdate) as "result" from employees;
+------------------------------------+
| result                             |
+------------------------------------+
| <EMP NAME="Tom">2020-01-01</EMP>   |
| <EMP NAME="Jerry">2020-02-01</EMP> |
+------------------------------------+
2 rows in set (0.008 sec)

2.4.2.4. XMLAGG

XMLAGG 用于将多个XML数据汇聚成单个XML数据，例如，在XMLELEMENT和XMLATTRIBUTES的例子中，我们将基础类型的employees表中的两行数据构造成了两条XML，这里可以使用XMLAGG将其汇聚。

OceanBase(SYS@SYS)>select xmlagg(xmlelement(emp, xmlattributes(name), empdate)) as "result" from employees;
+--------------------------------------------------------------------+
| result                                                             |
+--------------------------------------------------------------------+
| <EMP NAME="Tom">2020-01-01</EMP><EMP NAME="Jerry">2020-02-01</EMP> |
+--------------------------------------------------------------------+
1 row in set (0.007 sec)

2.4.3. 查询XML数据的表达式

2.4.3.1. EXTRACT

EXTRACT表达式，用于选取的XPath指定的内容，其返回值也是XMLType的数据；

OceanBase(SYS@SYS)>select id, extract(c1, '/employee/name') from xml_t;
+------+------------------------------+
| ID   | EXTRACT(C1,'/EMPLOYEE/NAME') |
+------+------------------------------+
|    1 | <name>Alice</name>
          |
|    2 | <name>Bob</name>
            |
+------+------------------------------+
2 rows in set (0.006 sec)/name') from xml_t;

2.4.3.2. EXTRACTVALUE

EXTRACTVALUE表达式与EXTRACT表达式类似，也选取XPath指定的内容，但其返回值是varchar2类型的数据，只会返回XPath指定路径的内容，不包括Element tag：

OceanBase(SYS@SYS)>select id, extractvalue(c1, '/employee/name') from xml_t;
+------+-----------------------------------+
| ID   | EXTRACTVALUE(C1,'/EMPLOYEE/NAME') |
+------+-----------------------------------+
|    1 | Alice                             |
|    2 | Bob                               |
+------+-----------------------------------+
2 rows in set (0.006 sec)

2.4.3.3. XMLSERIALIZE

XMLSERIALIZE用于将XMLType的数据转换为CLOB，BLOB或者VARCHAR2类型的数据，此表达式可以控制XML中各个元素的换行或者缩进行为。

OceanBase(SYS@SYS)>select xmlserialize(document c1 as varchar2(200) no indent) as res from xml_t where id=1;
+--------------------------------------------------------------------------------------------------------------------------------------+
| RES                                                                                                                                  |
+--------------------------------------------------------------------------------------------------------------------------------------+
| <?xml version="1.0" encoding="UTF-8"?>
<employee id="1">
<name>Alice</name>
<age>25</age>
<empdate>2019-03-14</empdate>
</employee>
 |
+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.007 sec)

2.4.3.4. XMLCAST

用于将XML中的内容转换为其它类型，例如NUMBER、VARCHAR2、CHAR、CLOB、BLOB及任何日期时间数据类型。

# number
OceanBase(SYS@SYS)>select xmlcast(xmlparse(CONTENT '<a>123.01</a>') as number) from dual;
+---------------------------------------------------+
| XMLCAST(XMLPARSE(CONTENT'<A>123.01</A>')ASNUMBER) |
+---------------------------------------------------+
|                                            123.01 |
+---------------------------------------------------+
1 row in set (0.004 sec)

# decimal
OceanBase(SYS@SYS)>select xmlcast(xmlparse(CONTENT '<a>123.01</a>') as decimal) from dual;
+----------------------------------------------------+
| XMLCAST(XMLPARSE(CONTENT'<A>123.01</A>')ASDECIMAL) |
+----------------------------------------------------+
|                                                123 |
+----------------------------------------------------+
1 row in set (0.005 sec)

# timestamp
OceanBase(SYS@SYS)>select xmlcast(xmlparse(CONTENT '<a>2023-04-03 15:13:00</a>') as timestamp) as res from dual;
+----------------------------+
| RES                        |
+----------------------------+
| 2023-04-03 15:13:00.000000 |
+----------------------------+
1 row in set (0.004 sec)

# date
OceanBase(SYS@SYS)>select xmlcast(xmlparse(CONTENT '<a>2023-04-03 15:13:00</a>') as date) as res from dual;
+---------------------+
| RES                 |
+---------------------+
| 2023-04-03 15:13:00 |
+---------------------+
1 row in set (0.004 sec)

2.4.4. 修改XML数据的表达式

2.4.4.1. UpdateXML

使用Update表达式，可以部分更新XML数据内的内容：

OceanBase(SYS@SYS)>SELECT c1 FROM xml_t WHERE id = 2;
+------------------------------------------------------------------------------------------------------------------------------------------+
| C1                                                                                                                                       |
+------------------------------------------------------------------------------------------------------------------------------------------+
| <?xml version="1.0" encoding="UTF-8"?>
<employee id="2">
  <name>Bob</name>
  <age>30</age>
  <empdate>2010-02-01</empdate>
</employee>
 |
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.006 sec)


OceanBase(SYS@SYS)>SELECT UPDATEXML(c1, '/employee/empdate/text()','2010-03-01') FROM xml_t
    -> WHERE id = 2;
+------------------------------------------------------------------------------------------------------------------------------------------+
| UPDATEXML(C1,'/EMPLOYEE/EMPDATE/TEXT()','2010-03-01')                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------+
| <?xml version="1.0" encoding="UTF-8"?>
<employee id="2">
  <name>Bob</name>
  <age>30</age>
  <empdate>2010-03-01</empdate>
</employee>
 |
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.007 sec)

3. 未来规划：支持XQuery、XPath、XSchema

本次XML特性在Oceanbase 4.2 商业版上发布，限Oracle租户使用。

当前XML 特性还不支持XQuery，对XPath的支持也还不全面，在后续版本中会持续补全这部分能力。未来也会考虑提供对XSchema的支持，以及基于XSchema的XML关系对象存储方式，关系对象存储可以提供更好的存储性能以及XML中片段的查询效率。

码农公寓