從Oracle 10g開始，甲骨文公司新增了XQuery和XMLTable兩個功能作為處理XML的武器。 XMLQuery一樣，您可以使用XQuery語言構造XML數據和查詢XML和關系數據。你可以使用XMLTable從XQuery查詢結果創建關系表和列。
本文我們將了解Oracle XMLTable函數，並且通過例子介紹XMLTable函數的用法。
考慮到員工會有一些XML數據，所以我們創建一個EMPLOYEES表：
Create TABLE EMPLOYEES
(
id NUMBER,
data XMLTYPE
)
表創建完成後，我們往表裡插入一些數據:
SQL Code
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>[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包含了員工的相關數據，在我們開始學習之前我們首先明確幾個數據：
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內容。
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
輸出：
不僅僅是text()表達式，Oracle還提供了其他很多有用的表達式，如 item(), node(), attribute(), element(), document-node(), namespace(), text(), xs:integer, xs:string。
讀取所選節點的屬性
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;
輸出：
讀取年齡超過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;
輸出：
本文由UncleToo翻譯整理，轉載請注明出處！
原文（英文）地址：http://viralpatel.net/blogs/oracle-xmltable-tutorial/
