【IT168 技术文档】在上一篇文章中，我介绍了MySQL对XML支持的部分功能，包括--xml命令行选项，以及MySQL 5.1.5中开始引入的新功能。今天我将介绍如何更好地格式化XML输出内容。
在MySQL中使用XML数据 http://tech.it168.com/a2009/1211/822/000000822827.shtml
在ExtractValue()函数输出中添加我们自己的标题
在上一篇文章中，我们使用LOAD_FILE()函数导入了一个XML文档，然后使用ExtractValue()函数提取了某些字段。
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);
SELECT xml_data FROM client_citizenship LIMIT 1 INTO @xml;
SELECT ExtractValue(@xml, '//row[2]/field[1]/@name'),
ExtractValue(@xml, '//row[2]/field[1]');
最后一个SELECT语句提取的是第二个
</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 |
+----------------------------------------------+----------------------------------------+
注意MySQL使用的是表达式作为列的标题，为了使用列字段名格式化结果集的标题，我们需要使用Prepared Statement，Prepared Statement是一种特殊类型的SQL语句，它是预编译的，这意味着Prepared Statement执行时，数据库不用再编译SQL了，这样可以有效减少执行时间。在这个例子中，使用Prepared Statement的好处是可以设置标题，然后连接到我们的查询字符串中。
SET @qry = CONCAT("SELECT ExtractValue(@xml, \"//row[2]/field[1]\") AS ", @header, ";");
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
现在我们看到的数据格式就是和我们平时看到的一样了。
| client_id |
+-----------+
| 2 |
+-----------+
在存储过程中使用ExtractValue()函数
上面使用的方法最大的缺陷就是在代码中写死了要检索的行，这样很不灵活，下面我们使用一个存储过程来解决这个问题。
MySqlDump
有Igor Romanenko编写的MySqlDump客户端最初是一个备份程序，它可以备份数据库，或将一个数据库转移到另一个数据库(MySQL或其它数据库)，它的这个备份过程其实是一个创建表，填充表的过程。此外，MySqlDump还可以生成CSV，XML或其它由分隔符控制的文本文件。
虽然不止一种执行MySqlDump的方法，但我想使用下面的语法：
你也可以使用标准的Unix/Dos方法输出到一个文件，如下面的命令将一个表转成一个XML文件：
打开sqldump.xml看看，我们发现MySqlDump客户端包括了比--xml选项更多的信息，但它却是按每一行、字段名和值的方式输出的，MySqlDump将表结构和表数据单独输出。
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="temp_table_article">
<table_structure name="client">
<field Field="client_id" Type="int(10) unsigned" Null="NO" Key="PRI" Extra="auto_increment" />
<field 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表的列中，下面是这个存储过程的全部代码。
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 temp_table_article < c:\xmldump_load.sql
你也可以使用source或\.命令从MySQL客户端载入存储过程。
OR
mysql> \. c:xmldump_load.sql;
这个存储过程接受下面三个输入参数：
XML输入文件名
目标数据库名
要创建的表名
我们还是以前面创建的sqldump.xml文件，数据库和表名为例。
这条命令将向client_info数据库client表中插入sqldump.xml文件中的内容。
即将推出的新特性
MySQL 6.0可以接受新的SQL语句，6.0.3版本将提供LOAD XML功能，直接导入XML文件，再也不用啥存储过程了，在写本文的时候还是5.4 Beta版本，因此还需耐心等候一段时间。
