您好,欢迎来到思海网络,我们将竭诚为您提供优质的服务! 诚征网络推广 | 网站备案 | 帮助中心 | 软件下载 | 购买流程 | 付款方式 | 联系我们 [ 会员登录/注册 ]
促销推广
客服中心
业务咨询
有事点击这里…  531199185
有事点击这里…  61352289
点击这里给我发消息  81721488
有事点击这里…  376585780
有事点击这里…  872642803
有事点击这里…  459248018
有事点击这里…  61352288
有事点击这里…  380791050
技术支持
有事点击这里…  714236853
有事点击这里…  719304487
有事点击这里…  1208894568
有事点击这里…  61352289
在线客服
有事点击这里…  531199185
有事点击这里…  61352288
有事点击这里…  983054746
有事点击这里…  893984210
当前位置:首页 >> 技术文章 >> 文章浏览
技术文章

如何在MySQL数据库中使用XML数据

添加时间:2014-2-17 16:42:34  添加: 思海网络 

  通过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_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格式结果:

<?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, "//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_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格式结果:

<?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, "//row[2]/field[1]/@name") |ExtractValue(@xml, '//row[2]/field[1]') |
+----------------------------------------------+----------------------------------------+
|client_id                                     |2                                       |
+----------------------------------------------+----------------------------------------+

  在下一篇文章中,我们将看到一种更好的格式化结果集的方法,以便列标题可以更好地展示。此外,我将会扩展上面的例子,使用存储过程检索整个XML文档,最后还将看到UpdateXML()函数的使用介绍。

关键字:MySQL、数据库、XML数据

分享到:

顶部 】 【 关闭
版权所有:佛山思海电脑网络有限公司 ©1998-2024 All Rights Reserved.
联系电话:(0757)22630313、22633833
中华人民共和国增值电信业务经营许可证: 粤B1.B2-20030321 备案号:粤B2-20030321-1
网站公安备案编号:44060602000007 交互式栏目专项备案编号:200303DD003  
察察 工商 网安 举报有奖  警警  手机打开网站