通过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 -p -e “SHOW VARIABLES LIKE ‘%version%'” –-xml
最终输出的XML包括下面的标准格式:
整个数据集都包括在节点中;
每一行对应一个节点;
所有列包括在一个节点中;
列名出处在name属性中;
列值是一个文本节点。
>
protocol_version
10
version
5.1.30-community
version_comment
MySQL Community Server (GPL)
version_compile_machine
ia32
version_compile_os
Win32
为了便于对比,下面给出上面的命令表格化输出结果。
+————————-+———————+
| 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_citizenship
SELECT 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_citizenship
2, 1944-01-15, F, 4, 20, 0
2, 1944-01-15, F, 7, 77, 1
当我加上–xml选项后,“SELECT *”查询将返回下面的XML格式结果:
>
2
1944-01-15
F
4
20
0
2
1944-01-15
F
7
77
1
使用–xml选项的缺点是从关联的数据到XML的映射是固定的,因此无法修改输出。如果你想做某些字符串操作,可以去掉–xml选项,按你自己的方式生成XML代码。
继续沿用前面的查询,我们再来看看如何修改输出的XML,使其符合下面的格式要求:
2
1944-01-15
F
7
77
1
其中一个可行的办法是使用CONCAT() 和 GROUP_CONCAT()字符串函数。
mysql>SELECT CONCAT(‘nn’,
->GROUP_CONCAT(”, client_id, ‘n’ SEPARATOR ”),
->GROUP_CONCAT(”,date_of_birth,’n’ SEPARATOR ”),
->GROUP_CONCAT(”,gender,’n’ SEPARATOR ”),
->GROUP_CONCAT(”,citizenship_id,’n’ SEPARATOR ”),
->GROUP_CONCAT(”,country_cd,’n’ SEPARATOR ”),
->GROUP_CONCAT(”,primary_citizenship,’n’ SEPARATOR ”),
->”) AS xmldoc
->FROM client_citizenshipG
虽然我们使用这种变通的方法达到了目标,但这样做还不如使用脚本或编程语言执行格式化,当然这得看你是否熟悉相关脚本或编程语言了。
使用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[2])
2 (field[1])
1944-01-15
…
下面显示的是上面的查询格式化输出结果:
+———————————————-+—————————————-+
|ExtractValue(@xml, “//row[2]/field[1]/@name”) |ExtractValue(@xml, ‘//row[2]/field[1]’) |
+———————————————-+—————————————-+
|client_id |2 |
+———————————————-+—————————————-+
我们一直都在努力坚持原创.......请不要一声不吭,就悄悄拿走。
我原创,你原创,我们的内容世界才会更加精彩!
【所有原创内容版权均属TechTarget,欢迎大家转发分享。但未经授权,严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】
微信公众号
TechTarget
官方微博
TechTarget中国