从XML文件批量插入MYSQL

问题描述：

如何从XML文件将数据加载到Mysql表? 有什么方法可以从XML文件读取数据并写入MySql数据库. 我在XML文件中有大量数据.

How can we load data to Mysql Tables from XML Files?? Is there any way to read data from XML Files and Write to MySql database.. I have a bulk of data in XML Files.

在此先感谢您的帮助.

答

尝试

Try the LOAD XML function (MySQL 6.0). Here's the sample code from the reference manual:

使用包含以下内容的XML文档 person.xml :

Using an XML document person.xml containing:

<?xml version="1.0"?>
<list>
  <person person_id="1" fname="Pekka" lname="Nousiainen"/>
  <person person_id="2" fname="Jonas" lname="Oreland"/>
  <person person_id="3"><fname>Mikael</fname><lname>Ronström</lname></person>
  <person person_id="4"><fname>Lars</fname><lname>Thalmann</lname></person>
  <person><field name="person_id">5</field><field name="fname">Tomas</field><field name="lname">Ulin</field></person>
  <person><field name="person_id">6</field><field name="fname">Martin</field><field name="lname">Sköld</field></person>
</list>

您将创建一个像这样的表:

you would create a table like so:

CREATE TABLE person (
    person_id INT NOT NULL PRIMARY KEY,
    fname VARCHAR(40) NULL,
    lname VARCHAR(40) NULL,
    created TIMESTAMP
);

，并使用以下命令导入XML:

and use the following command to import the XML:

LOAD XML LOCAL INFILE 'person.xml'
INTO TABLE person
ROWS IDENTIFIED BY '<person>';

或者如果您正在运行MySQL 5.0，则可以使用

Or if you're running MySQL 5.0 you can use LOAD_FILE to load the entire XML document as a string into a single column, and then parse it using MySQL's XPath functionality.

有关MySQL.com的这篇文章有更多详细信息:

This article on MySQL.com has more details: Using XML in MySQL 5.1 and 6.0.

相关推荐