在MySQL中使用XML数据―数据格式化

前沿：在MySQL中使用XML数据可以更好的管理数据，将所有数据进行了格式化，这个过程是怎么样的呢？

在存储过程中使用ExtractValue()函数

上面使用的方法最大的缺陷就是在代码中写死了要检索的行，这样很不灵活，下面我们使用一个存储过程来解决这个问题。

MySqlDump

有Igor Romanenko编写的MySqlDump客户端最初是一个备份程序，它可以备份数据库，或将一个数据库转移到另一个数据库(MySQL或其它数据库)，它的这个备份过程其实是一个创建表，填充表的过程。此外，MySqlDump还可以生成CSV，XML或其它由分隔符控制的文本文件。

虽然不止一种执行MySqlDump的方法，但我想使用下面的语法：

mysqldump --xmldatabasename[tables] 

你也可以使用标准的Unix/Dos方法输出到一个文件，如下面的命令将一个表转成一个XML文件：　

C:>mysqldump --xml temp_table_article client > c:\sqldump.xml 

打开sqldump.xml看看，我们发现MySqlDump客户端包括了比--xml选项更多的信息，但它却是按每一行、字段名和值的方式输出的，MySqlDump将表结构和表数据单独输出。

<?xml version=1.0?
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
<database name="temp_table_article">  
name="client">  
client_id Type=int(10)unsignedNull=NO"Key=PRI" Extra=auto_increment/>
Field="date_of_birth" Type="date" Null="YES" Key="" Extra="" />  
                 <field Field="gender" Type="char(1)" Null="YES" Key="" Extra="" />  
                 <field Field="logical_delete_indicator" Type="tinyint(1)" Null="NO" Key="" Default="0" Extra="" />  
                 <key Table="client" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="client_id"   
                   Collation="A" Cardinality="4" Null="" Index_type="BTREE" Comment="" />  
                 <key Table="client" Non_unique="0" Key_name="client_id" Seq_in_index="1" Column_name="client_id"   
                   Collation="A" Cardinality="4" Null="" Index_type="BTREE" Comment="" />  
                 <options Name="client" Engine="InnoDB" Version="10" Row_format="Compact" Rows="4" Avg_row_length="4096"   
                   Data_length="16384" Max_data_length="0" Index_length="16384" Data_free="10485760"   
                   Auto_increment="5"   
                   Create_time="2009-10-05 17:51:34" Collation="latin1_swedish_ci" Create_options=""   
                   Comment="" />  
         </table_structure>  
         <table_data name="client">  
         <row>  
                 <field name="client_id">1</field>  
                 <field name="date_of_birth">1976-02-12</field>  
                 <field name="gender">M</field>  
                 <field name="logical_delete_indicator">1</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="logical_delete_indicator">0</field>  
         </row>  
         <row>  
                 <field name="client_id">3</field>  
                 <field name="date_of_birth">1956-06-04</field>  
                 <field name="gender">M</field>  
                 <field name="logical_delete_indicator">1</field>  
         </row>  
         <row>  
                 <field name="client_id">4</field>  
                 <field name="date_of_birth">1938-11-19</field>  
                 <field name="gender">F</field>  
                 <field name="logical_delete_indicator">0</field>  
         </row>  
         </table_data>  
</database>  
</mysqldump> 

我们使用这个工具创建XML数据结构的原因是已经有存储过程可以将MySqlDump XML数据插入到表中，MySQL开发人员Alexander Barkov编写了这样一个存储过程xmldump_load，这个存储过程可以从mysqldump --xml命令输出的XML文档中提取数据，并将提取到的数据插入到MySQL表的列中，下面是这个存储过程的全部代码。

DELIMITER |  
DROP PROCEDURE IF EXISTS xmldump_load |  
CREATE PROCEDURE xmldump_load(     file_name VARCHAR(128),  
                          database_name VARCHAR(128),  
                          table_name VARCHAR(128))  
BEGIN 
  DECLARE xml TEXT;  
   DECLARE nrows INT;  
  DECLARE rownum INT DEFAULT 1;  
   DECLARE ncols INT;  
  DECLARE colnum INT DEFAULT 1;  
   DECLARE ins_list TEXT DEFAULT '';  
  DECLARE val_list TEXT DEFAULT '';  
   DECLARE tmp VARCHAR(255);  
   # 将XML文件的内容载入到字符串中  
  SET xml = LOAD_FILE(file_name);  
   # 获得这个表中<row>的数量  
  SET nrows = ExtractValue(xml,   
   'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row)');  
   # 获得这个表中<filed>的数量  
  SET ncols = ExtractValue(xml,   
'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[1]/field)');  
   # 对于每一个 <row>  
  WHILE rownum <= nrows DO  
    # 对于每一个 <field> (列)  
    WHILE colnum <= ncols DO  
      SET tmp = ExtractValue(xml,   
'/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]/@name');  
      SET ins_list = CONCAT(ins_list, tmp, IF(colnum<ncols, ',', ''));  
      SET tmp = ExtractValue(xml,   
'/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]');  
      SET val_list = CONCAT(val_list, '''', tmp ,'''', IF(colnum<ncols, ',', ''));  
      SET colnum = colnum + 1;  
    END WHILE;  
     SET @ins_text = CONCAT('INSERT INTO t1 (', ins_list, ') VALUES (', val_list, ')');  
     SET ins_list = '';  
    SET val_list = '';  
     PREPARE stmt FROM @ins_text;  
    EXECUTE stmt;  
     SET rownum = rownum + 1;  
    SET colnum = 1;  
  END WHILE;  
END |  
DELIMITER ; 

仔细阅读代码你就会发现其实这个存储过程使用了我们前面使用到的工具，如LOAD_FILE()和ExtractValue()函数，它只不过增加了两个嵌套的While循环，遍历每一行和列。

下面说说导入sqldump.xml文件的步骤。首先须创建xmldump_load存储过程。

C:> mysql db_name < text_file  
C:> mysql temp_table_article < c:xmldump_load.sql 

你也可以使用source或.命令从MySQL客户端载入存储过程。

mysql> source c:xmldump_load.sql;  
OR 
mysql> . c:xmldump_load.sql; 

这个存储过程接受下面三个输入参数：

我们还是以前面创建的sqldump.xml文件，数据库和表名为例。

mysql> call xmldump_load('c:sqldump.xml ', 'client_info', 'client'); 

这条命令将向client_info数据库client表中插入sqldump.xml文件中的内容。

即将推出的新特性

MySQL 6.0可以接受新的SQL语句，6.0.3版本将提供LOAD XML功能，直接导入XML文件，再也不用啥存储过程了，在写本文的时候还是5.4 Beta版本，因此还需耐心等候一段时间。
 

上文详细介绍了在MySQL中使用XML数据—数据格式化，这个看上去全是代码，可能理解起来并不是那么容易，所以就需要大家深入其中，认真去理解，希望对大家有用处。