Oracle XQuery 實戰 (二)：Repository XML 查詢、fn:collection 應用與 XML 分解為關係數據

一、Oracle XML DB Repository 訪問函數

• fn:doc(uri)：查詢 **單個** Repository 文件資源 (如 /public/employees/employees.xml)
• fn:collection(uri)：查詢 **同一目錄下多個** 文件資源 (如 /public/employees)

二、構建測試數據：PL/SQL 生成多文件

先有 employees.xml (來自 HR.EMPLOYEES)，再用 PL/SQL 基於 SCOTT.DEPT/EMP 生成 acc_dept.xml 存入同目錄：

DECLARE
  xmldoc XMLTYPE;
BEGIN
  SELECT XMLQuery(
    'for $j in ora:view("SCOTT", "dept")/ROW
     where $j/DEPTNO = 10
     return 
       {$j/DEPTNO, $j/DNAME}
        {
         for $i in ora:view("SCOTT", "emp")/ROW
         where $i/DEPTNO = $j/DEPTNO
         return {$i/EMPNO, $i/ENAME, $i/SAL}
       } 
     '
    RETURNING CONTENT) INTO xmldoc FROM DUAL;

  IF DBMS_XDB.CREATERESOURCE('/public/employees/acc_dept.xml', xmldoc) THEN
    DBMS_OUTPUT.PUT_LINE('Resource created');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Cannot create resource');
  END IF;
  COMMIT;
END;
/
-- 需以 SCOTT/TIGER 登入執行

三、fn:collection 跨文件聯合查詢

兩文件結構不同：

• employees.xml：根元素 <EMPLOYEES>
• acc_dept.xml：根元素 <DEPARTMENT>

使用 XPath // (任意深度後代) 忽略結構差異，直接定位 <EMPLOYEE>：

SELECT XMLQuery(
  'for $i in fn:collection("/public/employees")//EMPLOYEE
   where $i/SAL >= 5000
   order by $i/ENAME
   return $i'
  RETURNING CONTENT) FROM DUAL;

輸出 (合併兩文件符合條件的員工)

102De Haan17000
7839KING5000
100King24000
101Kochhar17000

四、XML 分解為關係數據：XMLTable 虛擬表

應用層處理關係數據時，用 XMLTable 將 XML 節點映射為 SQL 虛擬表列：

SELECT emps.empno, emps.ename, emps.sal
FROM XMLTable(
  'for $i in fn:collection("/public/employees")//EMPLOYEE
   where $i/SAL >= 5000
   return $i'
  COLUMNS
    empno NUMBER PATH '/EMPLOYEE/EMPNO',
    ename VARCHAR2(30) PATH '/EMPLOYEE/ENAME',
    sal   NUMBER PATH '/EMPLOYEE/SAL'
) emps;

輸出 (標準關係結果集)

EMPNO  ENAME       SAL
-----  ----------  -----
7839   KING        5000
100    King        24000
101    Kochhar     17000
102    De Haan     17000

五、關鍵技術總結

六、最佳實踐建議

1. Repository 目錄規劃：按業務域/數據源分目錄，便於 fn:collection 範圍控制
2. 文件命名規範：含業務日期/版本，支援按時間篩選
3. 大量文件慎用 fn:collection：每次查詢需掃描目錄元數據，建議配合 XMLIndex 或定期 ETL 入關係表
4. 類型顯式轉換：xs:string(), xs:integer(), xs:date() 避免隱式轉換錯誤
5. 命名空間處理：帶命名空間 XML 需在 XQuery 前綴聲明 declare namespace ns="...";