所有文章 标签 分类 关于 简体中文

Oracle-XMLTable-使用教程与实例

 收录于  类别 软件开发
     约 1833 字  预计阅读 4 分钟   次阅读

Oracle-XMLTable-使用教程与实例

甲骨文公司新增了XQuery和XMLTable两个功能作为处理XML的武器。 XMLQuery一样，您可以使用XQuery语言构造XML数据和查询XML和关系数据。你可以使用XMLTable从XQuery查询结果创建关系表和列。

并且通过例子介绍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中检索不同的信息。

使用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官方手册

在这个查询中,我们使用XMLTable函数从EMPLOYEES表解析XML内容。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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或者选定的节点。

输出：

在上面的教程中，我们读取到了firstname / lastname节点。

通常我们还需要获取节点的文本值，下面的例子中，我们选取/Employees/Employee/firstname路径，并使用text()获取节点的值。

下面查询employees中所有的firstname

1
2
3
4
5

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

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;

输出：

1
2
3
4
5
6

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;

输出：

1
2
3
4
5
6

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;

输出：

1
2
3
4
5
6
7

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;

输出：

更新于 2021-01-23
 Database
返回 | 主页