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

在上一篇文章中，我介绍了MySQL对XML支持的部分功能，包括--xml命令行选项，以及MySQL 5.1.5中开始引入的新功能。今天我将介绍如何更好地格式化XML输出内容。

在ExtractValue()函数输出中添加我们自己的标题

在上一篇文章中，我们使用LOAD_FILE()函数导入了一个XML文档，然后使用ExtractValue()函数提取了某些字段。

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);

　　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 @header = SELECT ExtractValue(@xml, "//row[2]/field[1]/@name");
SET @qry = CONCAT("SELECT ExtractValue(@xml, "//row[2]/field[1]") AS ", @header, ";");
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

现在我们看到的数据格式就是和我们平时看到的一样了。　

+-----------+
| client_id |
+-----------+
| 2           |
+-----------+