MariaDB – 處理 XML 格式

測試環境為 CentOS 8 x86_64 虛擬機.

• ExtractValue
  當我們把整個 XML 儲存在一個欄位 (建議使用 TEXT 資料型態欄位),可以再透過函數 ExtractValue 取出相對應的資料,更多關於 ExtractValue 請參考 – https://benjr.tw/102790
  要怎麼透過函數 ExtractValue 把 XML 格式的資料擷取出來呢!函數 ExtractValue 參數需指定 XPath 其格式如下.

  /tag

  位於最上(外)層 (Root Element , 根元素 , 父元素) 且名稱匹配 <tag/> <tag/> 內的資料.

  /tag1/tag2

  除了要匹配 <tag2> <tag2/> 外,還必須為 <tag1> <tag1/> 最上(外)層 (Root Element , 根元素 , 父元素) 的子元素.

  MariaDB [testdb]> SET @xml = '<a><b>X</b><b>Y</b></a>';
  Query OK, 0 rows affected (0.001 sec)
  
  MariaDB [testdb]> SELECT ExtractValue(@xml, '/a/b[1]');
  +-------------------------------+
  | ExtractValue(@xml, '/a/b[1]') |
  +-------------------------------+
  | X                             |
  +-------------------------------+
  1 row in set (0.000 sec)
  

  //tag

  匹配任何為 <tag> 的資料.

  MariaDB [testdb]> SELECT ExtractValue(@xml, '//b[1]');
  +------------------------------+
  | ExtractValue(@xml, '//b[1]') |
  +------------------------------+
  | X                            |
  +------------------------------+
  1 row in set (0.000 sec)
  
  MariaDB [testdb]> SELECT ExtractValue(@xml, '//b[2]');
  +------------------------------+
  | ExtractValue(@xml, '//b[2]') |
  +------------------------------+
  | Y                            |
  +------------------------------+
  1 row in set (0.000 sec)
  

  No Matching Element

  如果沒符合的資料時,並不是回傳 NULL 而是 “”(空字串),要怎麼判斷是否沒資料(回傳空字串)還是真的是得到的是空字串.

  MariaDB> SELECT ExtractValue('<a><b/></a>', '/a/b');
  +-------------------------------------+
  | ExtractValue('<a><b/></a>', '/a/b') |
  +-------------------------------------+
  |                                     |
  +-------------------------------------+
  1 row in set (0.00 sec)
  
  MariaDB> SELECT ExtractValue('<a><c/></a>', '/a/b');
  +-------------------------------------+
  | ExtractValue('<a><c/></a>', '/a/b') |
  +-------------------------------------+
  |                                     |
  +-------------------------------------+
  1 row in set (0.00 sec)
  

  上面範例一回傳的是有資料(內容物為空字串),範例二是沒有資料(一樣回傳空字串),我們要怎麼判斷,可以利用 COUNT() 函數.

  MariaDB> SELECT ExtractValue('<a><b/></a>', 'count(/a/b)');
  +-------------------------------------+
  | ExtractValue('<a><b/></a>', 'count(/a/b)') |
  +-------------------------------------+
  | 1                                   |
  +-------------------------------------+
  1 row in set (0.00 sec)
  
  MariaDB> SELECT ExtractValue('<a><c/></a>', 'count(/a/b)');
  +-------------------------------------+
  | ExtractValue('<a><c/></a>', 'count(/a/b)') |
  +-------------------------------------+
  | 0                                   |
  +-------------------------------------+
  1 row in set (0.01 sec)
  

  有資料的 COUNT() 為 1 ,沒資料的是 0 .