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_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_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 >
< 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()函数的使用介绍。