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>[email protected]</email> </Employee> <Employee emplid="2222" type="admin"> <firstname>Sherlock</firstname> <lastname>Homes</lastname> <age>32</age> <email>[email protected]</email> </Employee> <Employee emplid="3333" type="user"> <firstname>Jim</firstname> <lastname>Moriarty</lastname> <age>52</age> <email>[email protected]</email> </Employee> <Employee emplid="4444" type="user"> <firstname>Mycroft</firstname> <lastname>Holmes</lastname> <age>41</age> <email>[email protected]</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>[email protected]</email> </Employee> <Employee emplid="2222" type="admin"> <firstname>Sherlock</firstname> <lastname>Homes</lastname> <age>32</age> <email>[email protected]</email> </Employee> <Employee emplid="3333" type="user"> <firstname>Jim</firstname> <lastname>Moriarty</lastname> <age>52</age> <email>[email protected]</email> </Employee> <Employee emplid="4444" type="user"> <firstname>Mycroft</firstname> <lastname>Holmes</lastname> <age>41</age> <email>[email protected]</email> </Employee> </Employees>'),'/Employees/Employee/firstname'))) T; C -------------------------------------------------------------------------------- <firstname>John</firstname> <firstname>Sherlock</firstname> <firstname>Jim</firstname> <firstname>Mycroft</firstname>
标签:XML,com,employees,t1,employee,sh,Oracle,KingbaseES,节点 From: https://www.cnblogs.com/kingbase/p/15092861.html