views:

661

answers:

3

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.

A: 

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?

digitala
might be simpler to use generate CSV instead of SQL from XML, but the general idea is the easiest.
Javier
+1  A: 

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.

Calvin
Hi Calvin, I m using 5.1 Version and Have gone through ur Link, there I found the Procedure for Loaing XMLFile Data to MYSql, I have Used , It shows no Error but the Data hasn't been loaded in Table, I used it 2wice of 3rice, It shows now error but Fills nodata on TAble.Can you help me.
Ashok Gupta
Why don't you post the code you have into the question; that way we can take a closer look at what you're doing and perhaps figure out what's going on.
Calvin
+1  A: 

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