Bulk Insertion in MYSQL from XML Files

2020-02-06 04:50发布

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.

Thanks in Advance for help.

3条回答
萌系小妹纸
2楼-- · 2020-02-06 05:13

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

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

and use the following command to import the XML:

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

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.

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

查看更多
手持菜刀,她持情操
3楼-- · 2020-02-06 05:25

If your looking for a simple solution, Navicat has a pretty simple xml import function.

男人必须洒脱
4楼-- · 2020-02-06 05:33

Why not use XSL to translate the XML into standard SQL insert statements, then use the "source" command from within the mysql client to read the output?

登录 后发表回答