MySQL XML 資料 XPath 支援深度解析：ExtractValue 與 UpdateXML 函數實戰

XPath 是導覽與查詢 XML 文件的語言。MySQL 提供 ExtractValue() 與 UpdateXML() 兩大核心函數支援 XML 資料處理。

UpdateXML() 函數詳解

UpdateXML() 用於依據 XPath 表達式匹配並替換 XML 片段。語法：

UpdateXML(XML_frag, xpath_expr, new_xml)

基本範例

將 <city>Chicago</city> 替換為 <city>New York</city>：

SELECT @new_xml_node := UpdateXML('Chicago', '//city', 'New York') AS XML_node;

行為特性

• 若無匹配節點，返回原始 XML 字串
• 若匹配多個節點，依序替換每個匹配的子文字節點
• 無法區分「匹配到空元素」與「完全無匹配」，可搭配 ExtractValue() 配合 XPath count() 函數判斷

錯誤處理機制

• XPath 定位器無效 → 產生錯誤 (ERROR 1105)
• XML 結構不良 → 返回 NULL 並產生警告，可用 SHOW WARNINGS 查看
• 第三參數替換 XML 不檢查結構有效性

XPath 表達式中的變數應用 (MySQL 5.1.20+)

弱檢查變數 ($@variable_name)

不檢查型別與賦值狀態，拼寫錯誤不報錯，返回 NULL/none。範例：

SET @XML_cities = 'ChicagoOcean CityBaltimore';
SET @state_index_first = 1, @state_index_second = 2;
SELECT @state_index_first, ExtractValue(@XML_cities, '//state[$@state_index_first]') AS 'First City';
-- 結果: Chicago
SELECT @state_index_second, ExtractValue(@XML_cities, '//state[$@state_index_second]') AS 'Second City';
-- 結果: Ocean City
SELECT @state_index_third, ExtractValue(@XML_cities, '//state[$@state_index_third]') AS 'Third City';
-- 結果: NULL (變數未定義)

強檢查變數 ($variable_name)

需在預存程序中 DECLARE 宣告，具型別與值檢查。範例：

DELIMITER |
CREATE PROCEDURE get_cities(IN XML_data VARCHAR(255))
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE city_name VARCHAR(100);
  CREATE TEMPORARY TABLE cities (
    match_number INT UNSIGNED NOT NULL DEFAULT 0,
    name VARCHAR(100) NULL
  );
  SET city_name = ExtractValue(XML_data, '//state[$i]');
  WHILE city_name != '' DO
    INSERT INTO cities SELECT i, city_name;
    SET i = i + 1;
    SET city_name = ExtractValue(XML_data, '//state[$i]');
  END WHILE;
  SELECT * FROM cities;
  DROP TABLE cities;
END |
DELIMITER ;
CALL get_cities(@XML_cities);

小結

MySQL XML 功能雖有限制，但版本迭代持續增強。期待未來支援 JSON 等更精簡資料格式。