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 · Answer 1 · 2017-02-22T12:11:56.140

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 '17 at 12:11

answered Feb 22 '17 at 11:40

Shafiqul Islam

5,570
2
34
43

1

probably duplicate question http://stackoverflow.com/questions/5491056/how-to-import-xml-file-into-mysql-database-table-using-xml-load-function?rq=1 – Kishan Oza Feb 22 '17 at 11:56
i already used this code for my project so i write this and check this code also for more sure.. – Shafiqul Islam Feb 22 '17 at 12:01
yah but answers look identical. – Kishan Oza Feb 22 '17 at 12:02
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 – Shafiqul Islam Feb 22 '17 at 12:09
ohk then give upvote to my comment also :) happy coding @http://stackoverflow.com/users/2567989/shafiqul-islam – Kishan Oza Feb 22 '17 at 12:13
i will give you :) now its your turn ;P @http://stackoverflow.com/users/2567989/shafiqul-islam – Kishan Oza Feb 22 '17 at 12:38
thank you and i am sure my answer will work so i deserve it . ha ha ha – Shafiqul Islam Feb 22 '17 at 12:41

How to import XML datas into Mysql table?

1 Answers1