XML & Text File in MySQL

XML File in MySQL

LOAD XML Syntax

CREATE TABLE person (

    person_id INT NOT NULL PRIMARY KEY,

    fname VARCHAR(40) NULL,

    lname VARCHAR(40) NULL,

    created TIMESTAMP

);

Now suppose that we have a simple XML file person.xml, whose contents are as shown here:

<list>

  <person person_id="1" fname="Kapek" lname="Sainnouine"/>

  <person person_id="2" fname="Sajon" lname="Rondela"/>

  <person person_id="3"><fname>Likame</fname><lname>Örrtmons</lname></person>

  <person person_id="4"><fname>Slar</fname><lname>Manlanth</lname></person>

  <person><field name="person_id">5</field><field name="fname">Stoma</field>

    <field name="lname">Milu</field></person>

  <person><field name="person_id">6</field><field name="fname">Nirtam</field>

    <field name="lname">Sklöd</field></person>

  <person person_id="7"><fname>Sungam</fname><lname>Dulbåd</lname></person>

  <person person_id="8" fname="Sraref" lname="Encmelt"/>

</list>123456789101112

Each of the permissible XML formats discussed previously isrepresented in this example file.

To import the data in person.xml into the person table, you can use this statement:

mysql> LOAD XML LOCAL INFILE 'person.xml'

    ->   INTO TABLE person

    ->   ROWS IDENTIFIED BY '<person>';

mysql --xml -e "SELECT * FROM test.person" > person-dump.xml

cat person-dump.xml

<?xml version="1.0"?>

<resultset statement="SELECT * FROM test.person" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <row>

               <field name="person_id">1</field>

               <field name="fname">Kapek</field>

               <field name="lname">Sainnouine</field>

  </row>

  <row>

               <field name="person_id">2</field>

               <field name="fname">Sajon</field>

               <field name="lname">Rondela</field>

  </row>

  <row>

               <field name="person_id">3</field>

               <field name="fname">Likema</field>

               <field name="lname">Örrtmons</field>

  </row>

  <row>

               <field name="person_id">4</field>

               <field name="fname">Slar</field>

               <field name="lname">Manlanth</field>

  </row>

  <row>

               <field name="person_id">5</field>

               <field name="fname">Stoma</field>

               <field name="lname">Nilu</field>

  </row>

  <row>

               <field name="person_id">6</field>

               <field name="fname">Nirtam</field>

               <field name="lname">Sklöd</field>

  </row>

  <row>

               <field name="person_id">7</field>

               <field name="fname">Sungam</field>

               <field name="lname">Dulbåd</field>

  </row>

  <row>

               <field name="person_id">8</field>

               <field name="fname">Sreraf</field>

               <field name="lname">Encmelt</field>

  </row>

</resultset>

CREATE TABLE person2 LIKE person;

Query OK, 0 rows affected (0.00 sec)

mysql> LOAD XML LOCAL INFILE 'person-dump.xml'

    ->   INTO TABLE person2;

Query OK, 8 rows affected (0.01 sec)

Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM person2;

+-----------+--------+------------+---------------------+

| person_id | fname  | lname      | created             |

+-----------+--------+------------+---------------------+

|         1 | Kapek  | Sainnouine | 2007-07-13 16:18:47 |

|         2 | Sajon  | Rondela    | 2007-07-13 16:18:47 |

|         3 | Likema | Örrtmons   | 2007-07-13 16:18:47 |

|         4 | Slar   | Manlanth   | 2007-07-13 16:18:47 |

|         5 | Stoma  | Nilu       | 2007-07-13 16:18:47 |

|         6 | Nirtam | Sklöd      | 2007-07-13 16:18:47 |

|         7 | Sungam | Dulbåd     | 2007-07-13 16:18:47 |

|         8 | Sreraf | Encmelt    | 2007-07-13 16:18:47 |

+-----------+--------+------------+---------------------+

8 rows in set (0.00 sec)

Text File in MySQL

Load Data Syntax :
CREATE TABLE jokes
  (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  joke TEXT NOT NULL);
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes                   //File path
  FIELDS TERMINATED BY ''

  LINES TERMINATED BY '\n%%\n' (joke);