簡介
與以前的 DB2 版本相比,IBM® DB2® 9 for Linux, UNIX®, and Windows® 的主要改進是添加了新的 XML 功能。在 DB2 9 之前,可以將 XML 文檔存儲為 LOB,或者將傳入的 XML 數據分解到關系表中,這個過程需要使用 XML Extender。但是,DB2 9 使用戶能夠按照 XML 固有的層次化格式存儲和查詢 XML 數據 —— 這種技術稱為 pureXML™ 支持。現在,可以在 XML 數據上建立索引,並通過調用存儲過程執行分解。
本文討論如何在 SQL 存儲過程中使用 XML。我提供了許多代碼示例來幫助演示特定的技術要點。提供示例的目的在於幫助您理解,因此示例要盡可能簡單。為此,示例可能有點兒不夠真實。
注意:本文假設您是應用程序開發人員或 DBA,而且已經熟悉如何編寫 SQL 存儲過程。
XML 簡介
XML 表示 “可擴展標記語言(extensible markup language)”,這是一種表示信息的層次化方法,它的語法與 Html 非常相似。XML 文檔是一個由 “元素(element)” 或分支組成的樹結構。每個元素有名稱,可以包含文本值,可以具有一系列屬性(格式為 “name=value”),可以有子元素。允許重復的元素名稱,但是一個元素中的屬性名稱必須是惟一的。
XQuery 是一種用於 XML 的查詢語言,它基於 W3C 標准。XQuery 常用於在關系數據庫外查詢 XML 數據,這些數據可能存儲在文本文件中。XQuery 可以訪問 XML 數據(包括聯結)、循環、聲明的變量、IF/THEN/ELSE 語句和其他結構。
在 DB2 9 中有四種處理 XML 數據的方式:
一般 SQL
一般 SQL 由常規 SQL 語句和函數組成,它們沒有引用 XPath 或 XQuery。一般 SQL 只能將 XML 文檔作為完整的實體進行操作。可以在數據庫中插入和檢索 XML 文檔,以及在實體和文本之間進行轉換。但是,無法解析 XML 文檔的一部分,也無法應用任何謂詞。
帶嵌入 XQuery 的 SQL/XML
SQL/XML 包含三個操作 XML 數據的新函數,這些函數以嵌入的 XQuery 命令作為參數 —— XMLQUERY、XMLEXISTS 和 XMLTABLE。
XMLQUERY 用來從 XML 數據中提取信息。通過結合使用 XMLCAST 函數,可以從 XML 中派生出關系數據。
XMLEXISTS 對 XML 數據應用謂詞,常常用在 WHERE 子句中。
XMLTABLE 以關系表的形式提取 XML 數據。
XQuery
XQuery 現在是 DB2 數據庫引擎中的本機特性。有兩個用來訪問 DB2 數據的函數。第一個函數 db2-fn:xmlcolumn 向 XQuery 返回 XML 列的值。包含所有行;沒有謂詞。
帶嵌入 SQL 的 XQuery
還提供 db2-fn:sqlquery 函數來訪問 DB2 數據,它以一個 SELECT 語句作為參數。這個 SELECT 可以執行所需的任何操作,但是必須在 SELECT 列表中返回一個 XML 類型的列。
存儲過程中支持的 XML 功能
對於 DB2 9,SQL 存儲過程有一些用來處理 XML 數據的新功能。有一種新的數據類型 “XML”。不但可以將它用於表中的列,還可以用於參數和聲明的變量。DB2 9 還增強了 SELECT 和 UID 語句,增加了幾個新的 XML 函數。
這在三個方面增強了 SQL 存儲過程:
可以在以下位置使用新的 XML 函數(例如,XMLQUERY、XMLEXISTS、XMLTABLE):
在游標中
允許 SELECT 語法的任何地方,比如 SELECT INTO 和 VALUES
變量賦值,比如 SET 語句
XML 列可以:
在游標結果集中返回
綁定(?表示法)到 SELECT 和 UID 語句中
IF/THEN/ELSE 語句可以測試 XMLEXISTS 謂詞
可以從以下語句裝載 XML 類型的變量:
SELECT INTO 語句
VALUES INTO 語句
FETCH INTO 語句
EXECUTE INTO 語句
SET 語句
限制和解決方法
SQL 存儲過程中的 XML 功能有一些限制:
XQuery 查詢只能作為動態游標的一部分運行。
XML 變量在 COMMIT 或 ROLLBACK 之後不可用。
動態游標中的 XQuery
XQuery 命令可以出現在兩種不同的上下文中:
放在單引號中,作為 SQL/XML 函數 XMLQUERY、XMLEXISTS 和 XMLTABLE 的參數
SQL/XML 函數中嵌入的 XQuery 允許靜態 SQL 語句。在下面的示例中,變量 adrs 是一個 XML 列或 XML 類型的變量。
清單 1. SQL/XML 函數中嵌入的 XQuery
XMLQUERY('$d/cust/name' PASSING adrs AS "d")
注意: 這個示例只包含一個非常簡單的 XPath 表達式,但是 SQL/XML 函數(XMLQUERY、XMLEXISTS、XMLTABLE)可以包含任何有效的 XQuery,包括復雜的 FLWOR 表達式。
作為查詢
XQuery 查詢只能在動態游標中定義。以下代碼在 SQL 存儲過程中是不允許的:
清單 2. 在 SQL 存儲過程中不允許靜態 XQUERY
XQUERY
for $dept in db2-fn:XMLcolumn("DEPT.DEPTDOC")/dept
where $dept/@deptID="PR27"
return $dept/employee/name
在 COMMIT 或 ROLLBACK 之後的 XML 變量
XML 變量(包括參數和聲明的變量)在 COMMIT 或 ROLLBACK 之後不可用。如果試圖使用 XML 變量,而沒有首先給它們賦新值,就會產生下面這樣的錯誤:
清單 3. 在 COMMIT/ROLLBACK 之後收到的錯誤消息
SQL1354N An SQL variable in routine "XXX" is not available for reference due
to a recent commit or rollback Operation. SQLSTATE=560CE
如果需要在存儲過程中顯式地控制提交點,那麼考慮以下選項:
將變量的 XML 文檔傳遞進一個持久性的表中。使用 “持久性的” 表是因為聲明的全局臨時表當前不允許 XML 列。
將 XML 變量串行化成 VARCHAR,在 COMMIT 或 ROLLBACK 之後再解析它。
代碼示例
我們來研究一下如何在 DB2 存儲過程中處理 pureXML 數據。為此,我們將考慮幾個與常見編程場景相關的示例。
生成測試數據
盡管在開發期間可以從命令行測試存儲過程,但是可能需要創建虛構的 XML 文檔,它們將作為輸入參數發送。清單 4 提供一種生成測試數據的簡單技術:
清單 4. 生成測試數據(1)
CALL ex_proc
(
XMLPARSE(
DOCUMENT
'
<customer>
<custid>HX25</custid>
</customer>
'
)
)
;
為了清晰,前一個示例保留了許多空白,使 XML 標記處於單獨的行上。但是,也可以讓代碼更緊湊:
清單 5. 生成測試數據(2)
CALL ex_proc(XMLPARSE(DOCUMENT '<customer><custid>HX25</custid></customer>'));
聲明參數
新的 XML 數據類型可以作為參數傳遞進存儲過程中,可以采用任何模式(輸入、輸出或者同時作為輸入/輸出)。在聲明數據類型為 XML 時,不指定長度。與將 XML 數據作為 CLOB 或 VARCHAR 處理(這需要指定最大長度)相比,這是一項不錯的改進。
清單 6. 聲明參數
CREATE PROCEDURE process_cust
(
IN inpdoc XML,
OUT outdoc XML,
INOUT doc3 XML
)
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
...
END
聲明變量
變量可以聲明為 XML 類型。與參數的情況一樣,不指定長度。
清單 7. 聲明變量
CREATE PROCEDURE ex_proc
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
DECLARE v_xml XML;
...
END
變量賦值
除了從游標獲得數據之外,可以使用三種不同的技術將來自單行查詢的值裝載進變量中:
SET 語句
SELECT INTO
VALUES INTO
這三種技術都可以使用 SQL/XML 函數,尤其是 XMLQUERY。本節中後面的示例分別演示這些技術。
下面的所有示例都使用同樣的輸入/輸出參數列表、同樣的輸入數據和同樣的 SQL/XML 函數,並產生同樣的結果。將三個參數傳遞給存儲過程。第一個參數是一個輸入 XML 文檔。第二個參數是一個輸出 VARCHAR。第三個參數是一個輸出 XML 文檔。
這些示例:
演示如何使用 XMLCAST,在將 XML 數據提取為標准關系數據類型(例如,CHAR 或 INT)時常常使用這種方法。
演示將 XMLQUERY 的輸出賦值給 XML 類型的變量。
在 XMLQUERY 函數中,“PASSING inpdoc” 引用變量。如果在 SELECT 語句中使用 XMLQUERY,那麼 PASSING 子句可以引用表列或者存儲過程變量。
在調用存儲過程的輸出中,可以看到提取 XML 元素與提取 XML 元素值之間的差異。
盡管這個示例只將值賦給輸出參數,但是使用聲明的變量也很容易。
清單 8. 三個變量賦值示例的輸入參數 “inpdoc” 的示例數據
<customer>
<name>Jed Clampett</name>
<city>Beverly Hills</city>
</customer>
清單 9. 三個變量賦值示例的輸出
Value of output parameters
--------------------------
Parameter Name : P2
Parameter Value : Jed Clampett
Parameter Name : P3
Parameter Value : <name>Jed Clampett</name>
Return Status = 0
變量賦值 / SET 語句
我們首先考慮如何將 XMLQuery 函數的輸出返回給存儲過程中聲明的變量。在這裡,p2 將輸出轉換為 VARCHAR 值,而 p3 以 XML 形式返回輸出。
清單 10. 使用 SET 語句進行變量賦值
CREATE PROCEDURE ex_proc
(
IN inpdoc XML,
OUT p2 VARCHAR(30),
OUT p3 XML
)
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
SET p2 =
XMLCAST
(
XMLQUERY('$d/customer/name' PASSING inpdoc AS "d")
AS VARCHAR(30)
);
SET p3 = XMLQUERY('$d/customer/name'PASSING inpdoc AS "d");
END
變量賦值 / SELECT INTO
這個示例與前一個示例很相似。但是,這個版本使用完整的 SQL/XML SELECT 語句填充 p2 和 p3 變量。
清單 11. 使用 SELECT INTO 進行變量賦值
CREATE PROCEDURE ex_proc
(
IN inpdoc XML,
OUT p2 VARCHAR(30),
OUT p3 XML
)
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
SELECT XMLCAST
(
XMLQUERY('$d/customer/name' PASSING inpdoc AS "d")
AS VARCHAR(30)
)
INTO
p2
FROM
sysibm.sysdummy1
;
SELECT XMLQUERY('$d/customer/name' PASSING inpdoc AS "d")
INTO
p3
FROM
sysibm.sysdummy1
;
END
變量賦值 / VALUES INTO
還可以使用 VALUES 子句填充變量 p2 和 p3,如下例所示。
清單 12. 使用 VALUES INTO 進行變量賦值
CREATE PROCEDURE ex_proc
(
IN inpdoc XML,
OUT p2 VARCHAR(30),
OUT p3 XML
)
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
VALUES XMLCAST
(
XMLQUERY('$d/customer/name' PASSING inpdoc AS "d")
AS VARCHAR(30)
)
INTO
p2
;
VALUES XMLQUERY
(
'$d/customer/name' PASSING inpdoc AS "d"
)
INTO
p3
;
END
類型轉換錯誤
如果目標數據類型與實際數據不兼容,XMLCAST 函數會引發錯誤。例如,不能將字符串 “HX25” 轉換為整數。存儲過程可以捕獲類型轉換錯誤,如清單 13 到清單 15 所示:
清單 13. 類型轉換錯誤 —— 輸入參數 “inpdoc” 的示例數據
<customer>
<custid>HX25</custid>
</customer>
清單 14. 類型轉換錯誤
CREATE PROCEDURE ex_proc ( IN inpdoc XML )
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
DECLARE v_int INTEGER;
DECLARE v_badnum CHAR(1) DEFAULT 'N';
DECLARE CONTINUE HANDLER FOR SQLSTATE '10608'
SET v_badnum = 'Y';
SET v_int =
XMLCAST
(
XMLQUERY('$d/customer/custid' PASSING inpdoc AS "d")
AS INT
);
IF (v_badnum = 'Y') THEN
SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'Bad number';
END IF;
END
清單 15. 類型轉換錯誤 —— 輸出
SQL0438N Application raised error with diagnostic text: "Bad number".
SQLSTATE=75002
IF/THEN/ELSE 語句和 XMLEXISTS
可以使用 XMLEXISTS 在 IF/THEN/ELSE 語句中測試 XML 類型的變量。
清單 16. 涉及 IF/THEN/ELSE 的代碼示例
CREATE PROCEDURE ex_proc(IN inpdoc XML)
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
IF XMLEXISTS('$d/request[@action="add"]' PASSING inpdoc AS "d") THEN
SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'F(x) add';
ELSEIF XMLEXISTS('$d/request[@action="update"]' PASSING inpdoc AS "d") THEN
SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'F(x) update';
ELSE
SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'F(x) invalid';
END IF;
END
清單 17. IF/THEN/ELSE,輸入值(1)
<request action="add">
<name>Jean Luc Picard</name>
</request>
Output:
SQL0438N Application raised error with diagnostic text: "F(x) add".
SQLSTATE=75002
清單 18. IF/THEN/ELSE,輸入值(2)
<request action="update">
<name>Jean Luc Picard</name>
</request>
Output:
SQL0438N Application raised error with diagnostic text: "F(x) update".
SQLSTATE=75002
清單 19. IF/THEN/ELSE,輸入值(3)
<request action="delete">
<name>Jean Luc Picard</name>
</request>
Output:
SQL0438N Application raised error with diagnostic text: "F(x) invalid".
SQLSTATE=75002
PASSING 子句
XMLQUERY、XMLEXISTS 和 XMLTABLE 函數的 PASSING 子句可以傳遞多個變量。變量不必限於 XML 數據類型。下兩個示例演示兩種有意義的操作。
清單 20. 測試屬性值
CREATE PROCEDURE ex_proc
(
IN inpdoc XML,
IN search VARCHAR(20),
OUT id VARCHAR(30)
)
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
SET id =
XMLCAST(
XMLQUERY('$d/customers/customer[@id=$a]/name' PASSING inpdoc AS "d",
search AS "a"
)
AS VARCHAR(30)
)
;
END
另一個示例演示如何使用 XMLQUERY(和其他 SQL/XML 函數)的 PASSING 子句。如果需要編寫泛型代碼以便訪問以各種方式進行格式化的 XML 數據,那麼可以根據位置(第一個、第二個、第三個等等)訪問節點和屬性。
輸入參數 “inpdoc” 的示例數據
清單 21. 根據位置提取節點和屬性 —— 輸入數據
<order id="333" status="open">
<customer>
<custid>11029></custid>
<name>Johnny Depp</name>
<city>Hollywood</city>
</customer>
</order>
清單 22. 根據位置提取節點和屬性
CREATE PROCEDURE ex_proc
(
IN inpdoc XML,
IN attrpos INT,
OUT attrname VARCHAR(30),
OUT attrvalue VARCHAR(30),
IN nodepos INT,
OUT nodename VARCHAR(30),
OUT nodevalue VARCHAR(30)
)
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
VALUES
(
XMLCAST(
XMLQUERY('$d/order/@*[$a]/name()' PASSING inpdoc AS "d", attrpos AS "a")
AS VARCHAR(30)
),
XMLCAST(
XMLQUERY('$d/order/@*[$a]' PASSING inpdoc AS "d", attrpos AS "a")
AS VARCHAR(30)
),
XMLCAST(
XMLQUERY('$d/order/customer/*[$n]/name()' PASSING inpdoc AS "d", nodepos AS "n")
AS VARCHAR(30)
),
XMLCAST(
XMLQUERY('$d/order/customer/*[$n]' PASSING inpdoc AS "d", nodepos AS "n")
AS VARCHAR(30)
)
)
INTO
attrname,
attrvalue,
nodename,
nodevalue
;
END