Oracle XMLTable 函數完整教程：從 XML 到關聯表的高效轉換

Oracle XMLTable 函數完整教程：從 XML 到關聯表的高效轉換

自 Oracle 10g 起，Oracle 引入 XMLTable 函數，配合 XQuery/XPath，可將 XML 資料直接轉換為關聯式資料列/欄位，極大簡化 XML 解析與 ETL。本文系統介紹語法、XPath 基礎、實戰範例與進階用法。

語法結構

XMLTable(
  XQuery_expression
  PASSING xml_data
  COLUMNS
    column_name datatype PATH 'xpath_expression' [DEFAULT expr],
    ...
) [xmltable_alias]

• XQuery_expression：定義逐行迭代的 XPath（如 /Employees/Employee）。
• PASSING：輸入 XMLType 欄位或 XML 字面值。
• COLUMNS：定義輸出欄位名、資料型態、對應的相對 XPath；可用 text() 取節點文本、@attr 取屬性。

範例資料準備

CREATE TABLE employees (
  id NUMBER,
  data XMLTYPE
);

INSERT INTO employees VALUES (1, XMLTYPE(
'
  
    JohnWatson30johnwatson@sh.com
  
  
    SherlockHolmes32sherlock@sh.com
  
  
    JimMoriarty52jim@sh.com
  
  
    MycroftHolmes41mycroft@sh.com
  
'));

基礎範例

1. 解析所有員工的 firstname、lastname

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;

輸出：

2. 使用 text() 取節點文本值

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;

3. 讀取屬性

SELECT t.id, x.*
FROM employees t,
     XMLTable('/Employees/Employee' PASSING t.data
        COLUMNS firstname VARCHAR2(30) PATH 'firstname',
                type      VARCHAR2(30) PATH '@type') x;

輸出：包含 admin/user 區分。

4. 依屬性過濾（XPath 謂詞）

-- 僅 emplid=2222
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;

5. 複雜條件：type='admin' 且 age>40

SELECT t.id, x.*
FROM employees t,
     XMLTable('/Employees/Employee[@type="admin" and 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;

XPath 快速參考

進階用法

XML 輸出模式（動態欄位）

當 XML 結構不固定或不想硬編碼欄位時，使用 XML 關鍵字：

SELECT x.column_value.getClobVal() AS xml_fragment
FROM employees t,
     XMLTable('/Employees/Employee' PASSING t.data COLUMNS column_value XMLTYPE) x;

命名空間處理

SELECT x.*
FROM employees t,
     XMLTable(
       XMLNAMESPACES('https://example.com/ns' AS "ns"),
       '/ns:Employees/ns:Employee' PASSING t.data
       COLUMNS firstname VARCHAR2(30) PATH 'ns:firstname') x;

效能建議

• XMLType 欄位建立 XMLIndex：CREATE INDEX idx_emp_xml ON employees(data) INDEXTYPE IS XDB.XMLIndex;
• 避免在大量資料上使用 // 遞歸下降，改用確切路徑。
• 若頻繁查詢固定欄位，考慮建立 虛擬欄位 或 函數索引 物件化 XPath 結果。

總結

XMLTable 是 Oracle 內建最強大的 XML→關聯轉換工具，結合 XPath/XQuery 可實現複雜結構的解析、過濾、轉型。掌握 PASSING、COLUMNS PATH、text()/@attr、謂詞過濾 核心語法，即可應對絕大多數 XML ETL 與報表需求。配合 XMLIndex 可獲得生產級效能。