title: Synchronizing XML file to MySQL database
body: <p>My company uses an internal management software for storing products. They want to transpose all the products in a MySql database so they can do available their products on the company website. </p>
  
  <p>Notice: they will continue to use their own internal software. This software can exports all the products in various file format (including XML). </p>
  
  <p>The syncronization not have to be in real time, they are satisfied to syncronize the MySql database once a day (late night).</p>
  
  <p>Also, each product in their software has one or more images, then I have to do available also the images on the website.</p>
  
  <p>Here is an example of an XML export:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;export_management userid="78643"&gt;
      &lt;product id="1234"&gt;
          &lt;version&gt;100&lt;/version&gt;
          &lt;insert_date&gt;2013-12-12 00:00:00&lt;/insert_date&gt;
          &lt;warrenty&gt;true&lt;/warrenty&gt;
          &lt;price&gt;139,00&lt;/price&gt;
          &lt;model&gt;
              &lt;code&gt;324234345&lt;/code&gt;
              &lt;model&gt;Notredame&lt;/model&gt;
              &lt;color&gt;red&lt;/color&gt;
              &lt;size&gt;XL&lt;/size&gt;
          &lt;/model&gt;
          &lt;internal&gt;
              &lt;color&gt;green&lt;/color&gt;
              &lt;size&gt;S&lt;/size&gt;
          &lt;/internal&gt;
          &lt;options&gt;
              &lt;s_option&gt;aaa&lt;/s_option&gt;
              &lt;s_option&gt;bbb&lt;/s_option&gt;
              &lt;s_option&gt;ccc&lt;/s_option&gt;
              &lt;s_option&gt;ddd&lt;/s_option&gt;
              &lt;s_option&gt;eee&lt;/s_option&gt;
              &lt;s_option&gt;fff&lt;/s_option&gt;
        ...
              &lt;extra_option&gt;ggg&lt;/extra_option&gt;
              &lt;extra_option&gt;hhh&lt;/extra_option&gt;
              &lt;extra_option&gt;jjj&lt;/extra_option&gt;
              &lt;extra_option&gt;kkk&lt;/extra_option&gt;
        ...
          &lt;/options&gt;
          &lt;images&gt;
              &lt;image&gt;
                  &lt;small&gt;1234_0.jpg&lt;/small&gt;
              &lt;/image&gt;
              &lt;image&gt;
                  &lt;small&gt;1234_1.jpg&lt;/small&gt;
              &lt;/image&gt;
          &lt;/images&gt;
      &lt;/product&gt;
      &lt;product id="5321"&gt;
      ...
      &lt;/product&gt;
      &lt;product id="2621"&gt;
      ...
      &lt;/product&gt;
    ...
  &lt;/export_management&gt;
  </code></pre>
  
  <p>Some ideas for how can I do it?</p>
  
  <p>Please let me know if my question is not clear. Thanks</p>
  
  <hr>
  
  <p><strong>EDIT:</strong>
  I used a SQL like this for each table to fill them with the XML datas:</p>
  
  <pre><code>LOAD XML LOCAL INFILE '/products.xml' INTO TABLE table_name ROWS IDENTIFIED BY '&lt;tag_name&gt;';
  </code></pre>
  
  <p>Then, checking the tables content I can see that the field "id" (primary key) automatically has mantained itself the same for each respective product row in each tables. That's correct and suprisingly awesome!</p>
  
  <p>The problem now is for the parameter <code>&lt;options&gt;</code> because it contains sub-parameters with same name (<code>&lt;s_option&gt;</code> and <code>&lt;extra_option&gt;</code>). The values of these tags are always different (that is, there is no a specific list of values, they are inserted manually by an employee) and also I don't know how many are for each product. I read that storing them as an array is not so good but if it's the only simple solution I can get it.</p>