使用 SQL/XML 查詢 DB2 原生 XML 資料：從純 SQL 到 XMLQUERY、XMLTABLE、FLWOR 表達式的完整進階指南

環境與範例資料

DB2 Viper (DB2 9) Beta 環境，SAMPLE 資料庫雙表：

CREATE TABLE items (
  id INT PRIMARY KEY NOT NULL,
  brandname VARCHAR(30),
  itemname VARCHAR(30),
  sku INT,
  srp DECIMAL(7,2),
  comments XML
);

CREATE TABLE clients (
  id INT PRIMARY KEY NOT NULL,
  name VARCHAR(50),
  status VARCHAR(10),
  contactinfo XML
);

查詢工具

DB2 Command Editor (Control Center → Tools) 或命令列處理器 (CLP)。

1. 純 SQL：只能整欄存取 XML

SELECT * FROM clients;
SELECT id, name, contactinfo FROM clients WHERE status = 'Gold';
CREATE VIEW goldview AS SELECT id, name, contactinfo FROM clients WHERE status = 'Gold';

**限制**：無法對 XML 內部元素投影、限制、聯結、聚合。

2. SQL/XML 核心函數

**關鍵**：XPath/XQuery **大小寫敏感** (SQL 不敏感)，zip ≠ ZIP。

3. 依 XML 元素值過濾 (XMLEXISTS)

-- 正確：括號內為路徑謂詞
SELECT name FROM clients
WHERE XMLEXISTS('$c/Client/Address[zip="95116"]' PASSING clients.contactinfo AS "c");

-- 錯誤：等號在路徑外，雖執行成功但**不會過濾結果**
SELECT name FROM clients
WHERE XMLEXISTS('$c/Client/Address/zip="95116"' PASSING clients.contactinfo AS "c");

參數標記應用 (Java)：

String sql = "SELECT name FROM clients WHERE XMLEXISTS('$c/Client/Address[zip=$z]' PASSING contactinfo AS \"c\")";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "95116");

4. 投影 XML 元素值 (XMLQUERY)

-- 所有 Gold 客戶的 email (含標籤)
SELECT XMLQUERY('$c/Client/email' PASSING contactinfo AS "c")
FROM clients WHERE status = 'Gold';

-- 僅第一個 email
SELECT XMLQUERY('$c/Client/email[1]' PASSING contactinfo AS "c")
FROM clients WHERE status = 'Gold';

-- 過濾無 email 者
SELECT XMLQUERY('$c/Client/email[1]' PASSING contactinfo AS "c")
FROM clients WHERE status = 'Gold'
  AND XMLEXISTS('$c/Client/email' PASSING contactinfo AS "c");

輸出為 XML 序列：<email>user@domain.com</email>

5. 關聯式映射多元素 (XMLTABLE)

SELECT t.comment#, i.itemname, t.customerid, t.message
FROM items i,
XMLTABLE('$c/Comments/Comment' PASSING i.comments AS "c"
  COLUMNS
    comment#  INTEGER PATH 'CommentID',
    customerid INTEGER PATH 'CustomerID',
    message    VARCHAR(100) PATH 'Message'
) AS t;

輸出為標準 SQL 結果集 (欄位自動大寫)。

若需保留 XML 型別：

COLUMNS
  comment#  INTEGER PATH 'CommentID',
  customerid INTEGER PATH 'CustomerID',
  message    XML BY REF PATH 'Message'

6. 建立 XML 資料關聯視圖

CREATE VIEW commentview(itemid, itemname, commentid, message, mustrespond) AS
SELECT i.id, i.itemname, t.commentid, t.message, t.responserequested
FROM items i,
XMLTABLE('$c/Comments/Comment' PASSING i.comments AS "c"
  COLUMNS
    commentid INTEGER PATH 'CommentID',
    message VARCHAR(100) PATH 'Message',
    responserequested VARCHAR(100) PATH 'ResponseRequested'
) AS t;

**效能警示**：視圖查詢**不使用 XML 索引**。若含嚴格關聯謂詞 (如 i.id = ?) 可先用關聯索引過濾再套用 XML 謂詞緩解。

7. XML 與關聯資料聯結

SELECT clients.name, clients.status
FROM items, clients
WHERE XMLEXISTS('$c/Comments/Comment[CustomerID=$p]'
  PASSING items.comments AS "c", clients.id AS "p");

將 XML 內 CustomerID 與關聯欄位 clients.id 聯結。

8. SQL/XML 中嵌入 FLWOR 表達式

FLWOR = FOR, LET, WHERE, ORDER BY, RETURN (發音 flower)。

-- 等價於 XMLQUERY('$c/Client/email[1]')
SELECT name,
  XMLQUERY('for $e in $c/Client/email[1] return $e' PASSING contactinfo AS "c")
FROM clients WHERE status = 'Gold';

-- 轉換為 HTML 段落
SELECT XMLQUERY('for $e in $c/Client/email[1]/text() return <p>{$e}</p>' PASSING contactinfo AS "c")
FROM clients WHERE status = 'Gold';

{$e} 花括號表示**運算表達式值**，否則視為字面常數。

9. 關聯資料發布為 XML (發布函數)

範例：產品目錄 XML

SELECT XMLELEMENT(NAME "item",
  XMLELEMENT(NAME "id", id),
  XMLELEMENT(NAME "brand", brandname),
  XMLELEMENT(NAME "sku", sku)
)
FROM items WHERE srp < 100;

輸出：<item><id>4272</id><brand>Classy</brand><sku>981140</sku></item>

範例：依狀態分組客戶名單

SELECT XMLELEMENT(NAME "customerList",
  XMLAGG(
    XMLELEMENT(NAME "customer",
      XMLFOREST(name AS "fullName", status AS "status")
    )
    ORDER BY name
  )
)
FROM clients
GROUP BY status;

輸出三組 customerList (Gold/Silver/Standard)，各含多個 customer 子元素。

結語

SQL/XML 為關聯與 XML 搭橋：XMLEXISTS 過濾、XMLQUERY 投影/轉換、XMLTABLE 關聯化、FLWOR 進階邏輯、發布函數反向產出 XML。掌握這組工具，即可在 DB2 中流暢處理混合資料模型。