如何在MySQL數據庫中使用XML數據
通過XML使系統之間的數據交換變得更簡單,因為它與編程語言無關,剛引入XML的概念時,是通過一個腳本或應用程序解析XML 數據,將其轉換為適合于數據庫和底層系統的有效格式,后來,隨著數據庫技術的發展,數據庫開始支持XML數據,這樣就不用轉換程序了。今天我將給大家介紹 一下MySQL數據庫對XML的支持,著重介紹如何導入XML數據到MySQL數據庫中,以及如何從MySQL數據庫導出XML格式數據。
使用--xml選項將數據導出為XML格式
在MySQL 5.0之前的版本中,使用MySQL命令行客戶端以XML格式導出數據時有諸多限制,執行一個命令或查詢時使用--xml或-X選項告訴MySQL客戶端將結果輸出為XML數據,例如,下面的命令將輸出所有以version開頭的數據庫變量。
C:\>mysql -u <userid> -p<password> -e "SHOW VARIABLES LIKE '%version%'" –-xml最終輸出的XML包括下面的標準格式:
整個數據集都包括在節點中;
每一行對應一個節點;
所有列包括在一個節點中;
列名出處在name屬性中;
列值是一個文本節點。
<?xml version="1.0"?><resultset statement="SHOW VARIABLES LIKE '%version%'"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Variable_name">protocol_version</field>
<field name="Value">10</field>
</row>
<row>
<field name="Variable_name">version</field>
<field name="Value">5.1.30-community</field>
</row>
<row>
<field name="Variable_name">version_comment</field>
<field name="Value">MySQL Community Server (GPL)</field>
</row>
<row>
<field name="Variable_name">version_compile_machine</field>
<field name="Value">ia32</field>
</row>
<row>
<field name="Variable_name">version_compile_os</field>
<field name="Value">Win32</field>
</row>
</resultset>
為了便于對比,下面給出上面的命令表格化輸出結果。
+-------------------------+---------------------+| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.22-beta-debug |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | suse-linux-gnu |
+-------------------------+---------------------+
同樣的語法可以應用到SELECT語句中,在我以前的一篇文章“借助臨時表讓你的MySQL查詢效率更高”中,最開始的查詢中我就用logical_delete_indicator=0的記錄填充client_citizenship臨時表。
INSERT INTO client_citizenshipSELECT cl.client_id,
cl.date_of_birth,
cl.gender,
cit.citizenship_id,
cit.country_code,
cit.primary_citizenship
FROM temp_table_article.client AS cl,
temp_table_article.citizenship AS cit,
temp_table_article.client_citizenship_rel AS rel
WHERE cl.client_id = rel.client_id
AND cit.citizenship_id = rel.citizenship_id
AND cit.logical_delete_indicator = 0
AND cl.logical_delete_indicator = 0;
在這個臨時表上執行“SELECT *”查詢返回下面的記錄:
client_id, date_of_birth, gender, citizenship_id, country_code, primary_citizenship2, 1944-01-15, F, 4, 20, 0
2, 1944-01-15, F, 7, 77, 1
當我加上--xml選項后,“SELECT *”查詢將返回下面的XML格式結果:
<?xml version="1.0"?><resultset statement="SELECT * FROM client_citizenship"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="client_id">2</field>
<field name="date_of_birth">1944-01-15</field>
<field name="gender">F</field>
<field name="citizenship_id">4</field>
<field name="country_code">20</field>
<field name="primary_citizenship">0</field>
</row>
<row>
<field name="client_id">2</field>
<field name="date_of_birth">1944-01-15</field>
<field name="gender">F</field>
<field name="citizenship_id">7</field>
<field name="country_code">77</field>
<field name="primary_citizenship">1</field>
</row>
</resultset>
使用--xml選項的缺點是從關聯的數據到XML的映射是固定的,因此無法修改輸出。如果你想做某些字符串操作,可以去掉--xml選項,按你自己的方式生成XML代碼。
繼續沿用前面的查詢,我們再來看看如何修改輸出的XML,使其符合下面的格式要求:
<client><client_id>2</client_id>
<date_of_birth>1944-01-15</date_of_birth>
<gender>F</gender>
<citizenship_id>7</citizenship_id>
<country_code>77</country_code>
<primary_citizenship>1</primary_citizenship>
</client>
其中一個可行的辦法是使用CONCAT() 和 GROUP_CONCAT()字符串函數。
mysql>SELECT CONCAT('\n<client>\n',->GROUP_CONCAT('<client_id>', client_id, '</client_id>\n' SEPARATOR ''),
->GROUP_CONCAT('<date_of_birth>',date_of_birth,'</date_of_birth>\n' SEPARATOR ''),
->GROUP_CONCAT('<gender>',gender,'</gender>\n' SEPARATOR ''),
->GROUP_CONCAT('<citizenship_id>',citizenship_id,'</citizenship_id>\n' SEPARATOR ''),
->GROUP_CONCAT('<country_cd>',country_cd,'</country_cd>\n' SEPARATOR ''),
->GROUP_CONCAT('<primary_citizenship>',primary_citizenship,'</primary_citizenship>\n' SEPARATOR ''),
->'</client>') AS xmldoc
->FROM client_citizenship\G
雖然我們使用這種變通的方法達到了目標,但這樣做還不如使用腳本或編程語言執行格式化,當然這得看你是否熟悉相關腳本或編程語言了。
使用Load_File()函數導入XML數據
MySQL 5.1.5包括了兩個新的函數:ExtractValue()和UpdateXML()。
ExtractValue():使用XPath符號從XML字符串提取值。
UpdateXML():返回一個替代的XML片段。
MySQL中導入XML數據最常用的方法是使用LOAD_FILE()函數打開一個完整的XML文檔,將其存儲在一個變量中,然后將變量插入到一個表列 中。這里還是以client_citizenship表為例進行說明,但這一次它只包括兩個字段:一個自增長的ID和一個xml_data列(就是由它存 儲XML文檔),TEXT數據類型非常適合用在這個列上,因為它可以容納非常長的字符串。
CREATE TEMPORARY TABLE client_citizenship (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
xml_data TEXT NOT NULL
);
SET @xml = LOAD_FILE("c:\\client_citizenships.xml");
INSERT INTO client_citizenship VALUES (NULL, @xml);
現在我們可以使用ExtractValue()函數從xml_data字段檢索變量了,ExtractValue()函數有兩個參數,第一個是被檢查的 XML片段,第二個是XPath表達式。XPath是一門專門設計用于查詢XML文檔中節點信息的語言,在www.w3.org網站有完整的XPath介 紹。XPath參數在元素選擇器中可以包括冒號“:”,以支持命名空間。檢索數據包括兩步,首先從client_citizenship表獲得XML,然 后將其放進變量。
SELECT xml_data FROM client_citizenship LIMIT 1 INTO @xml;現在我們使用ExtractValue()函數,@xml作為第一個參數,XPath字符串作為第二個參數進行查詢:
SELECT ExtractValue(@xml, '//row[2]/field[1]/@name'),ExtractValue(@xml, '//row[2]/field[1]');
在前一個ExtractValue()調用中,XPath表達式檢索第二個中的第一個元素的name屬性的值,如果你再次調用,name屬性會包含列頭;第二個XPath表達式提取文本節點,當然包括字段的值了。
...</row>
<row> (row[2])
<field name="client_id">2</field> (field[1])
<field name="date_of_birth">1944-01-15</field>
...
下面顯示的是上面的查詢格式化輸出結果:
+----------------------------------------------+----------------------------------------+|ExtractValue(@xml, "http://row[2]/field[1]/@name") |ExtractValue(@xml, '//row[2]/field[1]') |
+----------------------------------------------+----------------------------------------+
|client_id |2 |
+----------------------------------------------+----------------------------------------+
在下一篇文章中,我們將看到一種更好的格式化結果集的方法,以便列標題可以更好地展示。此外,我將會擴展上面的例子,使用存儲過程檢索整個XML文檔,最后還將看到UpdateXML()函數的使用介紹。
通過XML使系統之間的數據交換變得更簡單,因為它與編程語言無關,剛引入XML的概念時,是通過一個腳本或應用程序解析 XML數據,將其轉換為適合于數據庫和底層系統的有效格式,后來,隨著數據庫技術的發展,數據庫開始支持XML數據,這樣就不用轉換程序了。今天我將給大 家介紹一下MySQL數據庫對XML的支持,著重介紹如何導入XML數據到MySQL數據庫中,以及如何從MySQL數據庫導出XML格式數據。
使用--xml選項將數據導出為XML格式
在MySQL 5.0之前的版本中,使用MySQL命令行客戶端以XML格式導出數據時有諸多限制,執行一個命令或查詢時使用--xml或-X選項告訴MySQL客戶端將結果輸出為XML數據,例如,下面的命令將輸出所有以version開頭的數據庫變量。
C:\>mysql -u <userid> -p<password> -e "SHOW VARIABLES LIKE '%version%'" –-xml最終輸出的XML包括下面的標準格式:
整個數據集都包括在節點中;
每一行對應一個節點;
所有列包括在一個節點中;
列名出處在name屬性中;
列值是一個文本節點。
<?xml version="1.0"?><resultset statement="SHOW VARIABLES LIKE '%version%'"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Variable_name">protocol_version</field>
<field name="Value">10</field>
</row>
<row>
<field name="Variable_name">version</field>
<field name="Value">5.1.30-community</field>
</row>
<row>
<field name="Variable_name">version_comment</field>
<field name="Value">MySQL Community Server (GPL)</field>
</row>
<row>
<field name="Variable_name">version_compile_machine</field>
<field name="Value">ia32</field>
</row>
<row>
<field name="Variable_name">version_compile_os</field>
<field name="Value">Win32</field>
</row>
</resultset>
為了便于對比,下面給出上面的命令表格化輸出結果。
+-------------------------+---------------------+| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.22-beta-debug |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | suse-linux-gnu |
+-------------------------+---------------------+
同樣的語法可以應用到SELECT語句中,在我以前的一篇文章“借助臨時表讓你的MySQL查詢效率更高”中,最開始的查詢中我就用logical_delete_indicator=0的記錄填充client_citizenship臨時表。
INSERT INTO client_citizenshipSELECT cl.client_id,
cl.date_of_birth,
cl.gender,
cit.citizenship_id,
cit.country_code,
cit.primary_citizenship
FROM temp_table_article.client AS cl,
temp_table_article.citizenship AS cit,
temp_table_article.client_citizenship_rel AS rel
WHERE cl.client_id = rel.client_id
AND cit.citizenship_id = rel.citizenship_id
AND cit.logical_delete_indicator = 0
AND cl.logical_delete_indicator = 0;
在這個臨時表上執行“SELECT *”查詢返回下面的記錄:
client_id, date_of_birth, gender, citizenship_id, country_code, primary_citizenship2, 1944-01-15, F, 4, 20, 0
2, 1944-01-15, F, 7, 77, 1
當我加上--xml選項后,“SELECT *”查詢將返回下面的XML格式結果:
<?xml version="1.0"?><resultset statement="SELECT * FROM client_citizenship"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="client_id">2</field>
<field name="date_of_birth">1944-01-15</field>
<field name="gender">F</field>
<field name="citizenship_id">4</field>
<field name="country_code">20</field>
<field name="primary_citizenship">0</field>
</row>
<row>
<field name="client_id">2</field>
<field name="date_of_birth">1944-01-15</field>
<field name="gender">F</field>
<field name="citizenship_id">7</field>
<field name="country_code">77</field>
<field name="primary_citizenship">1</field>
</row>
</resultset>
使用--xml選項的缺點是從關聯的數據到XML的映射是固定的,因此無法修改輸出。如果你想做某些字符串操作,可以去掉--xml選項,按你自己的方式生成XML代碼。
繼續沿用前面的查詢,我們再來看看如何修改輸出的XML,使其符合下面的格式要求:
<client><client_id>2</client_id>
<date_of_birth>1944-01-15</date_of_birth>
<gender>F</gender>
<citizenship_id>7</citizenship_id>
<country_code>77</country_code>
<primary_citizenship>1</primary_citizenship>
</client>
其中一個可行的辦法是使用CONCAT() 和 GROUP_CONCAT()字符串函數。
mysql>SELECT CONCAT('\n<client>\n',->GROUP_CONCAT('<client_id>', client_id, '</client_id>\n' SEPARATOR ''),
->GROUP_CONCAT('<date_of_birth>',date_of_birth,'</date_of_birth>\n' SEPARATOR ''),
->GROUP_CONCAT('<gender>',gender,'</gender>\n' SEPARATOR ''),
->GROUP_CONCAT('<citizenship_id>',citizenship_id,'</citizenship_id>\n' SEPARATOR ''),
->GROUP_CONCAT('<country_cd>',country_cd,'</country_cd>\n' SEPARATOR ''),
->GROUP_CONCAT('<primary_citizenship>',primary_citizenship,'</primary_citizenship>\n' SEPARATOR ''),
->'</client>') AS xmldoc
->FROM client_citizenship\G
雖然我們使用這種變通的方法達到了目標,但這樣做還不如使用腳本或編程語言執行格式化,當然這得看你是否熟悉相關腳本或編程語言了。
使用Load_File()函數導入XML數據
MySQL 5.1.5包括了兩個新的函數:ExtractValue()和UpdateXML()。
ExtractValue():使用XPath符號從XML字符串提取值。
UpdateXML():返回一個替代的XML片段。
MySQL中導入XML數據最常用的方法是使用LOAD_FILE()函數打開一個完整的XML文檔,將其存儲在一個變量中,然后將變量插入到一個表列 中。這里還是以client_citizenship表為例進行說明,但這一次它只包括兩個字段:一個自增長的ID和一個xml_data列(就是由它存 儲XML文檔),TEXT數據類型非常適合用在這個列上,因為它可以容納非常長的字符串。
CREATE TEMPORARY TABLE client_citizenship (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
xml_data TEXT NOT NULL
);
SET @xml = LOAD_FILE("c:\\client_citizenships.xml");
INSERT INTO client_citizenship VALUES (NULL, @xml);
現在我們可以使用ExtractValue()函數從xml_data字段檢索變量了,ExtractValue()函數有兩個參數,第一個是被檢查的 XML片段,第二個是XPath表達式。XPath是一門專門設計用于查詢XML文檔中節點信息的語言,在www.w3.org網站有完整的XPath介 紹。XPath參數在元素選擇器中可以包括冒號“:”,以支持命名空間。檢索數據包括兩步,首先從client_citizenship表獲得XML,然 后將其放進變量。
SELECT xml_data FROM client_citizenship LIMIT 1 INTO @xml;現在我們使用ExtractValue()函數,@xml作為第一個參數,XPath字符串作為第二個參數進行查詢:
SELECT ExtractValue(@xml, '//row[2]/field[1]/@name'),ExtractValue(@xml, '//row[2]/field[1]');
在前一個ExtractValue()調用中,XPath表達式檢索第二個中的第一個元素的name屬性的值,如果你再次調用,name屬性會包含列頭;第二個XPath表達式提取文本節點,當然包括字段的值了。
...</row>
<row> (row[2])
<field name="client_id">2</field> (field[1])
<field name="date_of_birth">1944-01-15</field>
...
下面顯示的是上面的查詢格式化輸出結果:
+----------------------------------------------+----------------------------------------+|ExtractValue(@xml, "http://row[2]/field[1]/@name") |ExtractValue(@xml, '//row[2]/field[1]') |
+----------------------------------------------+----------------------------------------+
|client_id |2 |
+----------------------------------------------+----------------------------------------+
在下一篇文章中,我們將看到一種更好的格式化結果集的方法,以便列標題可以更好地展示。此外,我將會擴展上面的例子,使用存儲過程檢索整個XML文檔,最后還將看到UpdateXML()函數的使用介紹。
關鍵字:MySQL、數據庫、XML數據
新文章:
- CentOS7下圖形配置網絡的方法
- CentOS 7如何添加刪除用戶
- 如何解決centos7雙系統后丟失windows啟動項
- CentOS單網卡如何批量添加不同IP段
- CentOS下iconv命令的介紹
- Centos7 SSH密鑰登陸及密碼密鑰雙重驗證詳解
- CentOS 7.1添加刪除用戶的方法
- CentOS查找/掃描局域網打印機IP講解
- CentOS7使用hostapd實現無AP模式的詳解
- su命令不能切換root的解決方法
- 解決VMware下CentOS7網絡重啟出錯
- 解決Centos7雙系統后丟失windows啟動項
- CentOS下如何避免文件覆蓋
- CentOS7和CentOS6系統有什么不同呢
- Centos 6.6默認iptable規則詳解