CODEWITHSUNDEEP
mysqlxml
yarek
yarek

Reputation: 12034

How to import XML datas into Mysql table?

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

Upvotes: 1

Views: 1454

Answers (1)

Shafiqul Islam
Shafiqul Islam

Reputation: 5690

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

Upvotes: 2

Related Questions