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)
# 创建含有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中片段的查询效率。