One of my colleagues was grabbing data from the web. The data grabbed was in XML format  and he asked me “Hey Rajan, Is there any way that we can insert these data into database?”.

I said, “Wait, let me look”. For till to date, I did database to xml but did not perform xml to database. To solve this I googled and get the solution.

The solution was MySql: LOAD XML

The complete solution is as below:

data.xml file

<?xml version=”1.0″ standalone=”yes” ?>

<getChainTypes>

<result>

<id>1018</id>

<name>Campanile</name>

</result>

<result>

<field name=’id’>1019</field>

<field name=’name’> Best Western </field>

</result>

</getChainTypes>

To import this data into database MySql table, first you need to create table. Precautions, The table columns name must match exactly the data containing tag name or to the tag attribute mentioned as name=’column_name’.

So let’s create table xmldata:
CREATE TABLE xmldata (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(40) NULL,
    created TIMESTAMP
);

Once you are done with this now we just need to import data!!!

mysql> LOAD XML LOCAL INFILE 'd:/path_to_/data.xml'
    ->   INTO TABLE xmldata
    ->   ROWS IDENTIFIED BY '<result>';
 

That’s it. You are done with it.

Advertisements