title: MySQL Load XML Using Both Tag names and attribute values as columns
body: <p>I'm having a problem loading XML into a database table using LOAD XML INFILE. An example of my XML file is:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;ProductList&gt;
    &lt;Product&gt;
      &lt;SKU&gt;ABC123&lt;/SKU&gt;
      &lt;Description&gt;The product name&lt;/Description&gt;
      &lt;Images&gt;
        &lt;Name&gt;ABC123.JPG&lt;/Name&gt;
        &lt;ImagePath Type="Large"&gt;path/to/large/ABC123.JPG&lt;/ImagePath&gt;
        &lt;ImagePath Type="Small"&gt;path/to/small/ABC123.JPG&lt;/ImagePath&gt;
      &lt;/Images&gt;
    &lt;/Product&gt;
  &lt;/ProductList&gt;
  </code></pre>
  
  <p>I have a database table defined as:</p>
  
  <pre><code>CREATE TABLE `images` (
    `SKU` varchar(25) NULL,
    `Name` varchar(100) NULL,
    `Large` varchar(100) NULL,
    `Small` varchar(100) NULL,
    KEY `Sku` (`SKU`) 
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>And I'm using the following query to load:</p>
  
  <pre><code>LOAD XML INFILE '/path/to/xml'
  INTO TABLE `images`
  ROWS IDENTIFIED BY '&lt;Images&gt;';
  </code></pre>
  
  <p>It's loading the Name value into the table but the Large and Small fields are not getting populated. In reading through the MySQL docs it looks like it can support reading attributes as column headings but it doesn't specify if it can do a mix of using both tag names and attribute values as column names in the same row set.</p>
  
  <p><a href="https://dev.mysql.com/doc/refman/5.6/en/load-xml.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.6/en/load-xml.html</a></p>
  
  <p>Can anyone help? Is it possible to read in the image information from the XML layout I have into a single table with one row per  record, populating all three fields of name, large, and small?</p>