load xml to mysql under debian linux

Question

I'm trying to load two xml files into mysql . both have the same issues . I'm only dealing with one here. I'm loading solar and geo thermal data from my home into msql.

either of these didn't work for me.

mysql> LOAD XML LOCAL INFILE '/var/www/geo-field.xml' INTO TABLE geo; Query OK, 0 rows affected (0.00 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0

==============

mysql> LOAD XML LOCAL INFILE '/var/www/geo-field.xml' INTO TABLE geo(date,time,F2L3_IN,F1L2_OUT,F3L2_IN,F1L2 _IN,F2L1_Middle,F2L3_OUT,F1L2_Middle,F3L3_IN,F2L3_Middle,F2L2_Middle,F1L3_OUT,F2L1_IN,F3L3_Middle,F3L1_IN,F1 L1_Middle,F3L3_OUT,F2L1_OUT,F1L1_OUT,F1L3_Middle,F2L2_IN,F3L1_Middle,F1L3_IN,F2L2_OUT,F3L2_OUT,F1L1_IN,F3L2_ Middle,F3L1_OUT); Query OK, 0 rows affected (0.00 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0

The file exists

pi@raspi-web:~$ ls -l *.xml rw-r--r-- 1 pi pi 2289 Jun 26 12:30 geo-field.xml

    cat geo-field.xml
   <?xml version="1.0" ?>
    <devices>
          <device>
         <name>date</name>
          <value>06/26/2013</value>
          </device>

          <device>
          <name>time</name>
          <value>11:31:03</value>
          </device>

          <device>
          <name>F2L3_IN</name>
          <value>73.174995</value>
          </device>

          <device>
          <name>F1L2_OUT</name>
          <value>75.199989</value>
          </device>

          <device>
          <name>F3L2_IN</name>
          <value>73.737495</value>
          </device>

          <device>
          <name>F1L2_IN</name>
          <value>75.087501</value>
         </device>

        <device>
         <name>F2L1_Middle</name>
         <value>70.812492</value>
         </device>

        <device>
        <name>F2L3_OUT</name>
        <value>75.424995</value>
      </device>

       <device>
       <name>F1L2_Middle</name>
       <value>71.150001</value>
       </device>

        <device>
      <name>F3L3_IN</name>
       <value>74.074989</value>
       </device>

       <device>
      <name>F2L3_Middle</name>
      <value>70.249992</value>
      </device>

     <device>
     <name>F2L2_Middle</name>
     <value>71.037498</value>
    </device>

    <device>
    <name>F1L3_OUT</name>
    <value>75.199989</value>
   </device>

   <device>
   <name>F2L1_IN</name>
   <value>75.424995</value>
   </device>

   <device>
   <name>F3L3_Middle</name>
   <value>70.137489</value>
   </device>

   <device>
   <name>F3L1_IN</name>
   <value>73.400001</value>
   </device>

   <device>
   <name>F1L1_Middle</name>
   <value>70.924995</value>
   </device>

   <device>
   <name>F3L3_OUT</name>
   <value>74.074989</value>
   </device>

   <device>
   <name>F2L1_OUT</name>
   <value>72.949989</value>
   </device>

   <device>
   <name>F1L1_OUT</name>
   <value>75.424995</value>
   </device>

   <device>
   <name>F1L3_Middle</name>
   <value>71.037498</value>
   </device>

   <device>
   <name>F2L2_IN</name>
   <value>75.312492</value>
   </device>

   <device>
   <name>F3L1_Middle</name>
   <value>69.912498</value>
   </device>

   <device>
   <name>F1L3_IN</name>
   <value>75.537498</value>
   </device>

   <device>
   <name>F2L2_OUT</name>
   <value>72.612495</value>
   </device>

   <device>
   <name>F3L2_OUT</name>
   <value>73.287498</value>
   </device>

   <device>
   <name>F1L1_IN</name>
   <value>75.199989</value>
   </device>

   <device>
   <name>F3L2_Middle</name>
   <value>72.275001</value>
   </device>

   <device>
   <name>F3L1_OUT</name>
   <value>73.062492</value>
   </device>

</devices>

Answer 1

The XML file you are trying to import is not formatted using a schema that MySQL knows how to import. You will need to convert it yourself.

The formats that MySQL will recognize are defined here:

http://dev.mysql.com/doc/refman/5.5/en/load-xml.html