KingbaseES 内置支持 XML 相关操作,也可以通过xml2 插件进行扩展支持。以下通过例子介绍 KingbaseES XML 与Oracle 在用法上存在的一些差异。
一、数据准备
create table t1(content xml);
insert into t1 values( xml ('<Employees>
<Employee emplid="1111" type="admin">
<firstname>John</firstname>
<lastname>Watson</lastname>
<age>30</age>
<email>johnwatson@sh.com</email>
</Employee>
<Employee emplid="2222" type="admin">
<firstname>Sherlock</firstname>
<lastname>Homes</lastname>
<age>32</age>
<email>sherlock@sh.com</email>
</Employee>
<Employee emplid="3333" type="user">
<firstname>Jim</firstname>
<lastname>Moriarty</lastname>
<age>52</age>
<email>jim@sh.com</email>
</Employee>
<Employee emplid="4444" type="user">
<firstname>Mycroft</firstname>
<lastname>Holmes</lastname>
<age>41</age>
<email>mycroft@sh.com</email>
</Employee>
</Employees>') );
二、XML节点标记方式
Expression | Description |
nodename | 选择所有名称为"nodename"的节点 |
/ | 选择根节点 |
// | 从当前节点选择文档中相匹配的节点,无论他们在哪里 |
. | 选择当前节点 |
.. | 选择当前节点的父节点 |
@ | 选择属性 |
employee | 选择所有名称为"employee"的节点 |
employees/employee | 选择所有子节点为employee的employees节点 |
//employee | 选择所有employee的元素,无论他们在哪里 |
Path Expression | Result |
/employees/employee[1] | 选择第一个employee节点,它是employees的子节点。 |
/employees/employee[last()] | 选择最后一个employee元素,它是employees的子节点 |
/employees/employee[last()-1] | 选择是employees子元素的倒数第二个employee元素 |
//employee[@type='admin'] | 选择所有具有与'admin'的值的属性命名类型的employee元素 |
三、Extract
1、Oracle
SQL> select extract(content,'/Employees/Employee/firstname') from t1;
EXTRACT(CONTENT,'/EMPLOYEES/EMPLOYEE/FIRSTNAME')
--------------------------------------------------------------------------------
<firstname>John</firstname>
<firstname>Sherlock</firstname>
<firstname>Jim</firstname>
<firstname>Mycroft</firstname>
2、KingbaseES
test=# select extract(content,'/Employees/Employee/firstname') from t1;
extract
--------------------------------------------------------------------------------------------------------------------
<firstname>John</firstname><firstname>Sherlock</firstname><firstname>Jim</firstname><firstname>Mycroft</firstname>
(1 row)
四、Xpath
XPath使用路径表达式来选择XML文档中的节点或节点列表。Oracle 没有该函数。
使用例子:
test=# select xpath('/Employees/Employee/firstname',content) from t1;
xpath
-------------------------------------------------------------------------------------------------------------------------
{<firstname>John</firstname>,<firstname>Sherlock</firstname>,<firstname>Jim</firstname>,<firstname>Mycroft</firstname>}
(1 row)
test=# select xpath('/Employees/Employee/firstname/text()',content) from t1;
xpath
-----------------------------
{John,Sherlock,Jim,Mycroft}
(1 row)
五、XMLTable
KingbaseES 有XMLTable 函数,Oracle 用 TABLE + XMLSequence 实现。
1、KingbaseES
test=# select c from t1,xmltable('/Employees/Employee/firstname' passing t1.content columns c xml path '.');
c
---------------------------------
<firstname>John</firstname>
<firstname>Sherlock</firstname>
<firstname>Jim</firstname>
<firstname>Mycroft</firstname>
(4 rows)
2、Oracle
select value(T) AS C from TABLE(XMLSequence(extract(XMLTYPE('<Employees>
<Employee emplid="1111" type="admin">
<firstname>John</firstname>
<lastname>Watson</lastname>
<age>30</age>
<email>johnwatson@sh.com</email>
</Employee>
<Employee emplid="2222" type="admin">
<firstname>Sherlock</firstname>
<lastname>Homes</lastname>
<age>32</age>
<email>sherlock@sh.com</email>
</Employee>
<Employee emplid="3333" type="user">
<firstname>Jim</firstname>
<lastname>Moriarty</lastname>
<age>52</age>
<email>jim@sh.com</email>
</Employee>
<Employee emplid="4444" type="user">
<firstname>Mycroft</firstname>
<lastname>Holmes</lastname>
<age>41</age>
<email>mycroft@sh.com</email>
</Employee>
</Employees>'),'/Employees/Employee/firstname'))) T;
C
--------------------------------------------------------------------------------
<firstname>John</firstname>
<firstname>Sherlock</firstname>
<firstname>Jim</firstname>
<firstname>Mycroft</firstname>