Import XML with content specified as attributes into a MySQL table?

Question

I have an XML file that looks like this:

<?xml version="1.0" encoding="utf-8"?>
<comments>
  <row Id="1" PostId="1" Score="3" Text="Maxis uses this in The Sims when clicking on your person to perform actions. ex: call-&gt;friend-&gt;[friends name]" CreationDate="2010-08-09T19:52:30.690" UserId="12" />
  <row Id="2" PostId="1" Score="1" Text="Here are some examples: http://en.wikipedia.org/wiki/Pie_menu#Notable_implementations" CreationDate="2010-08-09T19:54:54.007" UserId="31" />
  <row Id="3" PostId="9" Score="13" Text="How about making it short and with less steps :)" CreationDate="2010-08-09T19:55:57.670" UserId="35" />
  <row Id="4" PostId="3" Score="1" Text="10 sec seems like an awful lot when you are browsing." CreationDate="2010-08-09T19:57:57.057" UserId="32" />
  ...
</comments>

I've looked around and found this slightly related question, but it has rows that look like this:

<row>
  <field name="personal_number">539</field>
  <field name="firstname">Name</field>
  <field name="lastname">Surname</field>
  <field name="email">email.domain.com</field>
  <field name="start_time">2011-04-02 13:30:00</field>
  <field name="end_time">2011-04-02 18:15:00</field>
  <field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
</row>

Where each row has an XML element for each field - instead of a single element for each row with the content specified as attributes, as I have.

How can I import this into MySQL? I can make do with just about any technique, but I'd prefer to stay away from anything requiring Windows.

Answer 1

See http://dev.mysql.com/doc/refman/5.6/en/load-xml.html for the XML formats supported by the LOAD XML INFILE statement.

The manual page states:

the import routine automatically detects the format for each row and interprets it correctly. Tags are matched based on the tag or attribute name and the column name.

One of the formats supported does resemble yours -- storing columns as attributes of a <row> element.

If you need to transform your XML data into one of the formats supported by MySQL's LOAD XML, you could probably use xsltproc.

I would assume that if your XML data isn't formatted with perfect consistency, that it will confuse LOAD XML INFILE.