转载自:http://www.uncletoo.com/html/oracle/778.html
从Oracle 10g开始,甲骨文公司新增了XQuery和XMLTable两个功能作为处理XML的武器。 XMLQuery一样,您可以使用XQuery语言构造XML数据和查询XML和关系数据。你可以使用XMLTable从XQuery查询结果创建关系表和列。
本文我们将了解Oracle XMLTable函数,并且通过例子介绍XMLTable函数的用法。
考虑到员工会有一些XML数据,所以我们创建一个EMPLOYEES表:
1
2
3
4
5
|
Create
TABLE
EMPLOYEES
(
id NUMBER,
data XMLTYPE
);
|
表创建完成后,我们往表里插入一些数据:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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包含了员工的相关数据,在我们开始学习之前我们首先明确几个数据:
1、有4名员工在我们的XML文件
2、每个员工都有通过属性定义一个唯一的员工id emplid
3、每个员工也有一个属性type,定义雇员是否是管理员或用户。
4、每个员工都有四个子节点: firstname , lastname , age和email
5、年龄是多少
现在我们可以使用Oracle XMLTable函数从XML中检索不同的信息。
1、学习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元素 |
其他更多的表达式可以参考Oracle官方手册
2、Oracle XMLTable函数的基础知识
读取Employees中所有firstname和lastname
在这个查询中,我们使用XMLTable函数从EMPLOYEES表解析XML内容。
1
2
3
4
5
6
7
|
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函数的语法:
1
2
3
|
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
1
2
3
4
5
6
|
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;
|
输出:
不仅仅是text()表达式,Oracle还提供了其他很多有用的表达式,如 item(), node(), attribute(), element(), document-node(), namespace(), text(), xs:integer, xs:string。
读取所选节点的属性
XML节点定了相关属性,我们也可以读取到节点的这些属性,下面的查询是找出employee节点的type属性:
1
2
3
4
5
6
|
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读取特定的记录
1
2
3
4
5
6
7
|
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
1
2
3
4
5
6
7
|
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;
|
输出:
读取年龄超过40的所有员工的firstname 和 lastname
1
2
3
4
5
6
7
8
|
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;
|
输出: