mysql导出和导入xml文件实例

from: http://wenku.baidu.com/link?url=Iwr112wEjDzZbtSSn1EZkiZqH34vkjHtdAfZE9ntNQ9sAWf5G2Gaazu9VAEmzZGn15OnyYeNtwvM2HAShaNU7SjGZUbmfHqKcmLVTJ1xpf_

1，输出数据库版本变量为xml格式

Shell> mysql -X -uroot -proot -e "use test; show variables like '%version%';"
参数X表示生成xml格式的输出 , 参数e表示执行后面的命令


<?xml version="1.0"?>
<resultset statement="show variables like '%version%'">
<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.0.22-community-nt-log</field>
</row>


<row>
<field name="Variable_name">version_comment</field>
<field name="Value">MySQL Community Edition (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>


my test:
mysql -u ccc bank -e "select * from person;"
mysql -X -u ccc bank -e "select * from person;"


*********************************************************************
2，导出表数据到xml文件
Shell> mysql -X -uroot -proot -e "use test; select * from test;" > ./a.xml
Shell> more a.xml


<?xml version="1.0"?>
<resultset statement="select * from test">
<row>
<field name="id">111</field>
</row>


<row>
<field name="id">10</field>
</row>


<row>
<field name="id">1</field>
</row>


<row>
<field name="id">110</field>
</row>
</resultset>


my test:
mysql -u ccc bank -e "select * from person;" > ./mysql_noxml.txt
mysql -X -u ccc bank -e "select * from person;" > ./mysql_xml.xml


*********************************************************************
3，换一种方式导出表结构和内容，其中第一个db_name是数据库名，第二个test是表名
Shell> mysqldump --xml -uroot -proot db_name test


<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="db_name">
<table_structure name="test">
<field Field="id" Type="int(11)" Null="YES" Key="" Extra="" />
<options Name="test" Engine="InnoDB" Version="10" Row_format="Compact" Rows="4" Avg_row_length="4096" Data_length="16384" Max_data_length="0" Index_length="0" Data_free="0" Create_time
="2008-09-04 02:45:12" Collation="utf8_general_ci" Create_options="" Comment="InnoDB free: 11264 kB" />
</table_structure>
<table_data name="test">
<row>
<field name="id">111</field>
</row>
<row>
<field name="id">10</field>
</row>
<row>
<field name="id">1</field>
</row>
<row>
<field name="id">110</field>
</row>
</table_data>
</database>
</mysqldump>


my tests:
Shell> mysqldump --xml -u ccc bank person


*********************************************************************
4，导入xml文件的内容到数据库表，这里主要用到了load_file()函数
mysql> create table xmlt(
-> id int ,
-> doc blob
-> );


mysql> insert into xmlt values(1,load_file('/home/a.xml') );


mysql> select * from xmlt;
+------+--------------------------------------
----------------------------------------------
| id | doc


+------+--------------------------------------
----------------------------------------------
| 1 | <?xml version="1.0"?>


<resultset statement="select * from test">
<row>
<field name="id">111</field>
</row>


<row>
<field name="id">10</field>
</row>


<row>
<field name="id">1</field>
</row>


<row>
<field name="id">110</field>
</row>
</resultset>


后记：由此类推还可以使用存储过程的方式将xml数据导入和导出