How to import XML datas into Mysql table?

Question

Here is extract from XML:

<?xml version='1.0' encoding='iso-8859-15'?>
<annonces>
    <annonce>
        <transac>V</transac>
        <type_bien>Maison</type_bien>
        <texte>hello world</texte>
        <p_tel>1210212</p_tel>
        <ann_prix>10</ann_prix>
        <ann_cp>2870</ann_cp>
        <ann_ville>CREPY</ann_ville>
        <ann_dept>2</ann_dept>
        <ann_surface>246</ann_surface>
        <ann_nbpieces>7</ann_nbpieces>
        <first_parution>1999-01-01</first_parution>
        <first_prix>0 &amp;euro;</first_prix>
    </annonce>
    <annonce>
    .....
</annonces>

1) is it possible to import that into mysql and create the table automatically ?

2) I tried with

LOAD XML INFILE '/datas/data.xml' INTO TABLE data;

adn got as result

Query OK, 0 rows affected (0.00 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0

Shafiqul Islam · Accepted Answer · 2017-02-22 12:11:56Z

2

you can use this code , i think work for you

LOAD XML LOCAL INFILE '/datas/data.xml'  INTO TABLE data(field1,field2,field3.field4...);

you can also use this code

 LOAD XML LOCAL INFILE '/datas/data.xml' INTO TABLE  data;

you need to sure that file path ok and table name and filed value same as your xml file and it is not possible using single query insert and create use LOCAL before INFILE i think it will work

edited Feb 22, 2017 at 12:11

answered Feb 22, 2017 at 11:40

Shafiqul Islam

5,6852 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kishan Oza Over a year ago

probably duplicate question stackoverflow.com/questions/5491056/…

Shafiqul Islam Over a year ago

i already used this code for my project so i write this and check this code also for more sure..

Kishan Oza Over a year ago

yah but answers look identical.

Shafiqul Islam Over a year ago

i found this solution from other side and more sure i also search and see it this link also so i also give up vote for this link. i gave two answer in this question and write his/her 1 question also

Kishan Oza Over a year ago

ohk then give upvote to my comment also :) happy coding @stackoverflow.com/users/2567989/shafiqul-islam

|

Collectives™ on Stack Overflow

How to import XML datas into Mysql table?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related