從
Oracle 10g開(kāi)始，甲骨文公司新增了
XQuery和
XMLTable兩個(gè)功能作為處理XML的武器。 XMLQuery一樣，您可以使用XQuery語(yǔ)言構造XML數據和查詢(xún)XML和關(guān)系數據。你可以使用XMLTable從XQuery查詢(xún)結果創(chuàng )建關(guān)系表和列。
本文我們將了解
Oracle XMLTable函數，并且通過(guò)例子介紹XMLTable函數的用法。
考慮到員工會(huì )有一些XML數據，所以我們創(chuàng )建一個(gè)EMPLOYEES表：
1
2
3
4
5
Create TABLE EMPLOYEES
(
id NUMBER,
data XMLTYPE
);
表創(chuàng )建完成后，我們往表里插入一些數據:
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包含了員工的相關(guān)數據，在我們開(kāi)始學(xué)習之前我們首先明確幾個(gè)數據：
1、有4名員工在我們的XML文件
2、每個(gè)員工都有通過(guò)屬性定義一個(gè)唯一的員工id emplid
3、每個(gè)員工也有一個(gè)屬性type，定義雇員是否是管理員或用戶(hù)。
4、每個(gè)員工都有四個(gè)子節點(diǎn)： firstname ， lastname ， age和email
5、年齡是多少
現在我們可以使用
Oracle XMLTable函數從XML中檢索不同的信息。
1、學(xué)習XPath表達式
使用XMLTable函數之前最好知道一點(diǎn)關(guān)于
XPath。XPath使用路徑表達式來(lái)選擇XML文檔中的節點(diǎn)或節點(diǎn)列表?？聪旅娴牧斜恚?div style="height:15px;">
ExpressionDescription
nodename選擇所有名稱(chēng)為“nodename”的節點(diǎn)
/選擇根節點(diǎn)
//從當前節點(diǎn)選擇文檔中相匹配的節點(diǎn)，無(wú)論他們在哪里
.選擇當前節點(diǎn)
..選擇當前節點(diǎn)的父節點(diǎn)
@選擇屬性
employee選擇所有名稱(chēng)為“employee”的節點(diǎn)
employees/employee選擇所有子節點(diǎn)為employee的employees節點(diǎn)
//employee選擇所有employee的元素，無(wú)論他們在哪里
下面的表達式稱(chēng)為謂詞列表。謂詞在方括號中定義 [ ... ]。他們被用來(lái)找到一個(gè)特定的節點(diǎn)或包含一個(gè)特定值的節點(diǎn)。
Path ExpressionResult
/employees/employee[1]選擇第一個(gè)employee節點(diǎn),它是employees的子節點(diǎn)。
/employees/employee[last()]選擇最后一個(gè)employee元素，它是employees的子節點(diǎn)
/employees/employee[last()-1]選擇是employees子元素的倒數第二個(gè)employee元素
//employee[@type='admin']選擇所有具有與'admin'的值的屬性命名類(lèi)型的employee元素
其他更多的表達式可以參考
Oracle官方手冊2、Oracle XMLTable函數的基礎知識
讀取Employees中所有firstname和lastname
在這個(gè)查詢(xún)中,我們使用
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函數的語(yǔ)法：
1
2
3
XMLTable('<XQuery>'
PASSING <xml column>
COLUMNS <new column name> <column type> PATH <XQuery path>)
XMLTABLE函數包含一個(gè)XQuery行表達式和由一個(gè)或多個(gè)列表達式組成的COLUMNS子句。在上面的語(yǔ)句中，行表達式是 XPath /Employees/Employee。PASSING子句中的t.data指的是employees表中的XML列中的數據。
COLUMNS 子句用于將XML數據轉換成關(guān)系數據，這里每個(gè)參數都定義了一個(gè)列名和SQL數據類(lèi)型。在上面的查詢(xún)中，我們定義了firstname 和 lastname列并指向PATH的firstname 和 lastname或者選定的節點(diǎn)。
輸出：
使用text()讀取節點(diǎn)值
在上面的教程中，我們讀取到了firstname / lastname節點(diǎn)。通常我們還需要獲取節點(diǎn)的文本值，下面的例子中，我們選取/Employees/Employee/firstname路徑，并使用text()獲取節點(diǎn)的值。
下面查詢(xún)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。
讀取所選節點(diǎn)的屬性
XML節點(diǎn)定了相關(guān)屬性，我們也可以讀取到節點(diǎn)的這些屬性，下面的查詢(xún)是找出employee節點(diǎn)的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;
輸出：
讀取所有類(lèi)型是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;
輸出：
讀取年齡超過(guò)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;
輸出：
本文由UncleToo翻譯整理，轉載請注明出處！
原文（英文）地址：http://viralpatel.net/blogs/oracle-xmltable-tutorial/
