XMLTABLE¶
说明¶
XMLTable函数把XQuery的计算结果以表的形式返回。在sql中可以像访问表一样访问该函数的结果。（兼容Oracle、PostgreSQL）
返回值¶
返回xquery表达式查询到的多行结果
示例¶
SQL> select * from xmltable('/aa//text()' passing xmltype('<aa><bb>bbb</bb><cc>ccc</cc></aa>'));
COLUMN_VALUE
--------------------------------------------------------------------------------
bbb
ccc
其他用例¶
Create TABLE EMPLOYEES
(
id NUMBER,
data XMLTYPE
);
Insert INTO EMPLOYEES
VALUES (1, 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>'));
注解
- 注意：XML包含了员工的相关数据，首先明确几个数据：
- 有4名员工在我们的XML文件
- 每个员工都有通过属性定义一个唯一的员工id emplid
- 每个员工也有一个属性type，定义雇员是否是管理员或用户。
- 每个员工都有四个子节点： firstname ， lastname ， age和email
可以使用Oracle XMLTable函数从XML中检索不同的信息。
了解XPath表达式¶
使用XMLTable函数之前最好知道一点关于XPath。XPath使用路径表达式来选择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元素 |
XMLTable函数的基础知识¶
读取Employees中所有firstname和lastname
在这个查询中，我们使用XMLTable函数从EMPLOYEES表解析XML内容。
Select t.id, x.*
FROM employees t,
XMLTABLE ('/Employees/Employee'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname') x
Where t.id = 1;
XMLTable函数的语法：
XMLTable('<XQuery>'
PASSING <xml column>
COLUMNS <new column name> <column type> PATH <XQuery path>)
XMLTABLE函数包含一个XQuery行表达式和由一个或多个列表达式组成的COLUMNS子句。 在上面的语句中，行表达式是 XPath /Employees/Employee 。PASSING子句中的 t.data指的是employees表中的XML列中的数据。 COLUMNS 子句用于将XML数据转换成关系数据，这里每个参数都定义了一个列名和SQL数据类型。 在上面的查询中，我们定义了firstname 和 lastname列并指向PATH的firstname 和 lastname或者选定的节点。
输出结果：
使用text()读取节点值¶
在上面我们读取到了firstname / lastname节点。
通常我们还需要获取节点的文本值，下面的例子中，我们选取/Employees/Employee/firstname路径，并使用text()获取节点的值。
下面查询employees中所有的firstname
Select t.id, x.*
FROM employees t,
XMLTABLE ('/Employees/Employee/firstname'
PASSING t.data
COLUMNS firstname VARCHAR2 (30) PATH 'text()') x
Where t.id = 1;
输出结果：
读取所选节点的属性¶
XML节点定了相关属性，我们也可以读取到节点的这些属性，下面的查询是找出employee节点的type属性：
Select emp.id, x.*
FROM employees emp,
XMLTABLE ('/Employees/Employee'
PASSING emp.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
type VARCHAR2(30) PATH '@type') x;
输出结果：
使用ID读取特定的记录¶
Select t.id, x.*
FROM employees t,
XMLTABLE ('/Employees/Employee[@emplid=2222]'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname') x
Where t.id = 1;
输出结果：
读取所有类型是admin的员工的firstname 和 lastname¶
Select t.id, x.*
FROM employees t,
XMLTABLE ('/Employees/Employee[@type="admin"]'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname') x
Where t.id = 1;
输出结果：
注解
注：神通xmltable函数，需要在UTF8编码情况下能正常返回结果，在GBK情况下。
- 神通xmltable函数有一个ENABLE_XQUERY参数，可以取值 0、1、2，目前在不同取值下，对同一SQL语句会结果不同。
读取年龄超过40的所有员工的firstname 和 lastname¶
Select t.id, x.*
FROM employees t,
XMLTABLE ('/Employees/Employee[age>40]'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname',
age VARCHAR2(30) PATH 'age') x
Where t.id = 1;
输出结果：
