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