问题描述
如何从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.
在此先感谢您的帮助.
推荐答案
尝试 LOAD XML 函数(MySQL 6.0).这是参考手册中的示例代码:
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,则可以使用 LOAD_FILE 将整个XML文档作为字符串加载到单个列中,然后使用MySQL的XPath功能对其进行解析.
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的这篇文章有更多详细信息:在MySQL 5.1和6.0中使用XML .
This article on MySQL.com has more details: Using XML in MySQL 5.1 and 6.0.
这篇关于从XML文件批量插入MYSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!