SQL – XML

XML (Extensible Markup Language) 是一種可延伸標示語言.
相較於 HTML 是定義好的國際通用的標記式語言,我們是無法自訂標記(標籤 Tag),只能變更其屬性值,這時候可以使用 XML 來自定,讓電腦之間可以處理自定的資訊,這邊來看在 MariaDB 資料庫如何處理這些自定的標記(標籤 Tag).

測試環境為 CentOS 8 x86_64 (虛擬機)

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Database changed

LOAD XML INFILE

第一種處理 XML 模式,是將 XML 的欄位轉換到相對應的資料庫欄位,MySQL (Mariadb) 支援以下這幾種 XML 格式.

    <row column1="value1" column2="value2" .../>
<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>
<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>

建立要使用的 XML 格式檔案(同一個檔案可以混合不同格式).

[root@localhost ~]# vi /var/ftp/file.xml
<Computer>
<row Id="1" Owner="Ben1" CreationDate="2020-05-03 19:54:30" DisplayName="Ben Mac" Model="MacBookPro15" CPU="Quad-Core Intel Core i5" MEM="16 GB" />
<row>
  <Id>2</Id>
  <Owner>Ben2</Owner>
  <CreationDate>2020-05-03 19:54:30</CreationDate>
  <DisplayName>Ben Mac</DisplayName>
  <Model>MacBookPro15</Model>
  <CPU>Quad-Core Intel Core i5</CPU>
  <MEM>16 GB</Memory>
</row>
<row>
  <field name='Id'>3</field>
  <field name='Owner'>Ben3</field>
  <field name='CreationDate'>2020-05-03 19:54:30</field>
  <field name='DisplayName'>Ben Mac</field>
  <field name='Model'>MacBookPro13.3</field>
  <field name='CPU'>Quad-Core Intel Core i5</field>
</row>
</Computer>

先定義我們所需的資料表,看你要儲存什麼欄位 ( Column ) ,就定義相對應的欄位 (通常建議使用 TEXT 資料型態欄位),下面我定義了全部資料欄位.

MariaDB [testdb]> CREATE TABLE xmltbl (ID  VARCHAR(100), Owner  VARCHAR(100), CreationDate TIMESTAMP, DisplayName  VARCHAR(100), Model  VARCHAR(100), CPU VARCHAR(100) , MEM  VARCHAR(100) );
Query OK, 0 rows affected (0.009 sec)
  1. ROWS IDENTIFIED BY
    剛剛建立資料表欄位與 XML 格式是一對一,可以用下面的指令來做.

    MariaDB [testdb]> LOAD XML LOCAL INFILE '/var/ftp/file.xml' INTO TABLE xmltbl ROWS IDENTIFIED BY '<row>';
    Query OK, 3 rows affected (0.003 sec)                
    Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
    

    可以注意到 ID 欄位為 NULL ,是因為資料表欄位名稱為 ID 而 XML 檔案為 Id.另外一個是第三筆沒有 Memory 資料,所以當找不到相對應的 XML 欄位時會填為 NULL .

    MariaDB [testdb]> SELECT * FROM xmltbl;
    +------+-------+---------------------+-------------+----------------+-------------------------+-------+
    | ID   | Owner | CreationDate        | DisplayName | Model          | CPU                     | MEM   |
    +------+-------+---------------------+-------------+----------------+-------------------------+-------+
    | NULL | Ben1  | 2020-05-03 19:54:30 | Ben Mac     | MacBookPro15   | Quad-Core Intel Core i5 | 16 GB |
    | NULL | Ben2  | 2020-05-03 19:54:30 | Ben Mac     | MacBookPro15   | Quad-Core Intel Core i5 | 16 GB |
    | NULL | Ben3  | 2020-05-03 19:54:30 | Ben Mac     | MacBookPro13.3 | Quad-Core Intel Core i5 | NULL  |
    +------+-------+---------------------+-------------+----------------+-------------------------+-------+
    3 rows in set (0.001 sec)
    

    針對上面遇到的問題修改一下 XML 檔案.

    [root@localhost ~]# vi /var/ftp/file1.xml
    <Computer>
    <row ID="1" Owner="Ben1" CreationDate="2020-05-03 19:54:30" DisplayName="Ben Mac" Model="MacBookPro15" CPU="Quad-Core Intel Core i5" MEM="16 GB" />
    <row>
      <ID>2</ID>
      <Owner>Ben2</Owner>
      <CreationDate>2020-05-03 19:54:30</CreationDate>
      <DisplayName>Ben Mac</DisplayName>
      <Model>MacBookPro15</Model>
      <CPU>Quad-Core Intel Core i5</CPU>
      <MEM>16 GB</Memory>
    </row>
    <row>
      <field name='ID'>3</field>
      <field name='Owner'>Ben3</field>
      <field name='CreationDate'>2020-05-03 19:54:30</field>
      <field name='DisplayName'>Ben Mac</field>
      <field name='Model'>MacBookPro13.3</field>
      <field name='CPU'>Quad-Core Intel Core i5</field>
      <field name='MEM'>16 GB</field>
    </row>
    </Computer>
    

    先清除剛剛的內容,在匯入一次.

    MariaDB [testdb]> TRUNCATE TABLE xmltbl;
    Query OK, 0 rows affected (0.012 sec)
    
    MariaDB [testdb]> LOAD XML LOCAL INFILE '/var/ftp/file1.xml' INTO TABLE xmltbl ROWS IDENTIFIED BY '<row>';
    Query OK, 3 rows affected (0.002 sec)                
    Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
    
    MariaDB [testdb]> SELECT * FROM xmltbl;
    +------+-------+---------------------+-------------+----------------+-------------------------+-------+
    | ID   | Owner | CreationDate        | DisplayName | Model          | CPU                     | MEM   |
    +------+-------+---------------------+-------------+----------------+-------------------------+-------+
    | 1    | Ben1  | 2020-05-03 19:54:30 | Ben Mac     | MacBookPro15   | Quad-Core Intel Core i5 | 16 GB |
    | 2    | Ben2  | 2020-05-03 19:54:30 | Ben Mac     | MacBookPro15   | Quad-Core Intel Core i5 | 16 GB |
    | 3    | Ben3  | 2020-05-03 19:54:30 | Ben Mac     | MacBookPro13.3 | Quad-Core Intel Core i5 | 16 GB |
    +------+-------+---------------------+-------------+----------------+-------------------------+-------+
    3 rows in set (0.001 sec)
    
  2. SET
    先清除剛剛的內容,利用不同的方式再匯入一次.

    MariaDB [testdb]> TRUNCATE TABLE xmltbl;
    Query OK, 0 rows affected (0.012 sec)
    

    這方式是將相對應的欄位透過相對應的變數儲存起來.

    LOAD XML LOCAL INFILE '/var/ftp/file.xml'
    INTO TABLE xmltbl (@Id, @Owner, @CreationDate, @DisplayName, @Model, @CPU, @MEM ) 
    SET ID=@Id, Owner=@Owner, CreationDate=@CreationDate, DisplayName=@DisplayName, Model=@Model, CPU=@CPU, MEM =@MEM;
    
    MariaDB [testdb]> LOAD XML LOCAL INFILE '/var/ftp/file.xml' INTO TABLE xmltbl (@Id, @Owner, @CreationDate, @DisplayName, @Model, @CPU, @MEM ) SET ID=@Id, Owner=@Owner, CreationDate=@CreationDate, DisplayName=@DisplayName, Model=@Model, CPU=@CPU, MEM =@MEM;
    Query OK, 3 rows affected (0.003 sec)                
    Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
    

    在儲存時指定 ID=@Id (因為資料表欄位名稱為 ID 而 XML 檔案為 Id).另外一個是第三筆沒有 Memory 資料,所以當找不到相對應的 XML 欄位時會填為 NULL .

    MariaDB [testdb]> SELECT * FROM xmltbl;
    +------+-------+---------------------+-------------+----------------+-------------------------+-------+
    | ID   | Owner | CreationDate        | DisplayName | Model          | CPU                     | MEM   |
    +------+-------+---------------------+-------------+----------------+-------------------------+-------+
    | 1    | Ben1  | 2020-05-03 19:54:30 | Ben Mac     | MacBookPro15   | Quad-Core Intel Core i5 | 16 GB |
    | 2    | Ben2  | 2020-05-03 19:54:30 | Ben Mac     | MacBookPro15   | Quad-Core Intel Core i5 | 16 GB |
    | 3    | Ben3  | 2020-05-03 19:54:30 | Ben Mac     | MacBookPro13.3 | Quad-Core Intel Core i5 | NULL  |
    +------+-------+---------------------+-------------+----------------+-------------------------+-------+
    3 rows in set (0.001 sec)
    

LOAD_FILE + ExtractValue

第二種方式是把整個 XML 儲存在一個欄位 (建議使用 TEXT 資料型態欄位),再透過函數 ExtractValue 取出相對應的資料.

建立要使用的 XML(file2.xml) 格式檔案.

[root@localhost ~]# vi /var/ftp/file2.xml
<Computer>
  <row Id="1" Owner="Ben" CreationDate="2020-05-03 19:54:30" DisplayName="Ben Mac" Model="MacBookPro15" CPU="Quad-Core Intel Core i5" Memory="16 GB" />
  <row Id="2" Owner="Ben" CreationDate="2020-05-01 19:54:30" DisplayName="Ben OLD Mac" Model="MacBook Air" CPU="Intel Ivy Bridge Core i5" Memory="4 GB" />
</Computer>

建立資料表並把 file2.xml 整個儲存起來.

MariaDB [testdb]> CREATE TABLE xmlextract (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, xml_data TEXT NOT NULL); 
Query OK, 0 rows affected (0.008 sec)
MariaDB [testdb]> SET @xml = LOAD_FILE("/var/ftp/file2.xml"); 
Query OK, 0 rows affected (0.001 sec)

MariaDB [testdb]> INSERT INTO xmlextract (xml_data) VALUES (@xml);
Query OK, 1 row affected (0.003 sec)

檢視一下資料.

MariaDB [testdb]> SELECT * FROM xmlextract\G
*************************** 1. row ***************************
      id: 1
xml_data: <Computer>
  <row Id="1" Owner="Ben" CreationDate="2020-05-03 19:54:30" DisplayName="Ben Mac" Model="MacBookPro15" CPU="Quad-Core Intel Core i5" Memory="16 GB" />
  <row Id="2" Owner="Ben" CreationDate="2020-05-01 19:54:30" DisplayName="Ben OLD Mac" Model="MacBook Air" CPU="Intel Ivy Bridge Core i5" Memory="4 GB" />
</Computer>

1 row in set (0.001 sec)

函數說明:
LOAD_FILE 函數指定載入檔案位置(只限定位於 MySQL Server Host 上的檔案),需注意權限問題(該使用者需有 File 的權限,可使用 SQL 指令 SHOW PRIVILEGES; 來檢視),無法讀取時 LOAD_FILE 函數會回傳 NULL .得到 NULL 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.

另外一個可能是當檔案大小超出 MYSQL 的預設時 max_allowed_packet 也無法存取,可以使用SQL 指令 SHOW VARIABLES LIKE ‘max_allowed_packet’; 來檢視.

要怎麼透過函數 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 .

透過剛剛的語法把資料擷取出來.

MariaDB [testdb]> SELECT xml_data FROM xmlextract WHERE id = 1 INTO @xml;
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> SELECT ExtractValue(@xml, '//Computer/row[1]/@DisplayName') , ExtractValue(@xml, '//Computer/row[2]/@DisplayName');
+------------------------------------------------------+------------------------------------------------------+
| ExtractValue(@xml, '//Computer/row[1]/@DisplayName') | ExtractValue(@xml, '//Computer/row[2]/@DisplayName') |
+------------------------------------------------------+------------------------------------------------------+
| Ben Mac                                              | Ben OLD Mac                                          |
+------------------------------------------------------+------------------------------------------------------+
1 row in set (0.001 sec)

遇過的問題

使用 LOAD_FILE 匯入 XML(編碼 UTF-16) 但資料表欄位變成亂碼 (欄位已選擇 UTF-16),後來是先將檔但從 UTF-16 轉換成為 UTF-8 再匯入才沒產生亂碼.
Linux 下可以使用者令來轉換

iconv -f UTF-16LE -t UTF-8 <filename> -o <new-filename>

Windows 下的 PowerShell 可以使用指令來轉換 (沒試過)

WideCharToMultiByte
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料