MySQL: Extracting data from an unusually formatted XML file

Question

I have an XML file with entries that look like this:

<card name="Fire Toad" id="7366070c-c74d-4bb9-b3d8-23177e887073">
    <property name="Sphere" value="Nature" />
    <property name="Cost" value="1" />
    <property name="Type" value="Creature" />
    <property name="Subtype" value="Animal Toad" />
    <property name="Attack" value="3" />
    <property name="Defense" value="2" />
    <property name="Gems" value="B" />
    <property name="Rules" value="" />
    <property name="Flavor" value="Fire toads have the uncanny ability to cook their food while chewing." />
    <property name="Rarity" value="Common" />
    <property name="Series" value="1" />
    <property name="Number" value="106" />
    <property name="Illustrator" value="Kevin Shoemaker" />
</card>

I want to extract data from it (for each card: name, id, and all of the properties) and insert it into a table in a MySQL database. It would be great if I could use the built-in LOAD XML statement, but unfortunately it doesn't support my file's formatting. What would be the most convenient way to do this?

Answer 1

Consider XSLT which much like SQL is a declarative, special-purpose language but designed to transform XML sources to specific formats like the XML needed for MySQL's LOAD XML method. XSLT can be run by command line with Bash/Powershell, any general purpose language (Java, C#, PHP, Perl, Python, R, VB), dedicated processors like Saxon/Xalan, even your everyday Excel!

In fact you can even run XSLT directly from MySQL's command line client by sending a shell command to terminal. Here is an xsltproc (processor available for Linux/Mac) call:

mysql> \! xsltproc -o /path/to/Output.xml /path/to/Script.xsl /path/to/Input.xml

Below script parses down to card node and migrates property children to new element and value.

XSLT (save as .xsl, a special .xml file)

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="/root">  
        <data> 
            <xsl:apply-templates select="cards"/>
        </data>
    </xsl:template>

    <xsl:template match="cards">  
        <xsl:apply-templates select="card"/>
    </xsl:template>

    <xsl:template match="card"> 
        <row>
           <name><xsl:value-of select="@name"/></name>
           <id><xsl:value-of select="@id"/></id>
           <xsl:apply-templates select="property"/>
        </row>       
    </xsl:template>

    <xsl:template match="property">
    <xsl:element name="{@name}"> 
        <xsl:value-of select="@value"/>
    </xsl:element>
    </xsl:template>

</xsl:stylesheet>

Input XML (assuming a structure in this format, adjust root name in XSLT)

<?xml version="1.0"?>
<root>
  <more_nodes/>
  <still_nodes/>
  <cards>
    <card name="Fire Toad 1" id="7366070c-c74d-4bb9-b3d8-23177e887073">
      <property name="Sphere" value="Nature 1"/>
      <property name="Cost" value="1"/>
      <property name="Type" value="Creature 1"/>
      <property name="Subtype" value="Animal Toad 1"/>
      <property name="Attack" value="3"/>
      <property name="Defense" value="2"/>
      <property name="Gems" value="B1"/>
      <property name="Rules" value=""/>
      <property name="Flavor" value="Fire toads have the uncanny ability to cook their food while chewing. 1"/>
      <property name="Rarity" value="Common 1"/>
      <property name="Series" value="1"/>
      <property name="Number" value="106"/>
      <property name="Illustrator" value="Kevin Shoemaker 1"/>
    </card>
    <card name="Fire Toad 2" id="7366070c-c74d-4bb9-b3d8-23177e887073">
      <property name="Sphere" value="Nature 2"/>
      <property name="Cost" value="1"/>
      <property name="Type" value="Creature 2"/>
      <property name="Subtype" value="Animal Toad 2"/>
      <property name="Attack" value="3"/>
      <property name="Defense" value="2"/>
      <property name="Gems" value="B2"/>
      <property name="Rules" value=""/>
      <property name="Flavor" value="Fire toads have the uncanny ability to cook their food while chewing. 2"/>
      <property name="Rarity" value="Common 2"/>
      <property name="Series" value="1"/>
      <property name="Number" value="106"/>
      <property name="Illustrator" value="Kevin Shoemaker 2"/>
    </card>
    <card name="Fire Toad 3" id="7366070c-c74d-4bb9-b3d8-23177e887073">
      <property name="Sphere" value="Nature 3"/>
      <property name="Cost" value="1"/>
      <property name="Type" value="Creature 3"/>
      <property name="Subtype" value="Animal Toad 3"/>
      <property name="Attack" value="3"/>
      <property name="Defense" value="2"/>
      <property name="Gems" value="B3"/>
      <property name="Rules" value=""/>
      <property name="Flavor" value="Fire toads have the uncanny ability to cook their food while chewing. 3"/>
      <property name="Rarity" value="Common 3"/>
      <property name="Series" value="1"/>
      <property name="Number" value="106"/>
      <property name="Illustrator" value="Kevin Shoemaker 3"/>
    </card>
  </cards>
  <other_nodes/>
</root>

Output XML (ready for MySQL's LOAD XML)

<?xml version="1.0"?>
<data>
  <row>
    <name>Fire Toad 1</name>
    <id>7366070c-c74d-4bb9-b3d8-23177e887073</id>
    <Sphere>Nature 1</Sphere>
    <Cost>1</Cost>
    <Type>Creature 1</Type>
    <Subtype>Animal Toad 1</Subtype>
    <Attack>3</Attack>
    <Defense>2</Defense>
    <Gems>B1</Gems>
    <Rules/>
    <Flavor>Fire toads have the uncanny ability to cook their food while chewing. 1</Flavor>
    <Rarity>Common 1</Rarity>
    <Series>1</Series>
    <Number>106</Number>
    <Illustrator>Kevin Shoemaker 1</Illustrator>
  </row>
  <row>
    <name>Fire Toad 2</name>
    <id>7366070c-c74d-4bb9-b3d8-23177e887073</id>
    <Sphere>Nature 2</Sphere>
    <Cost>1</Cost>
    <Type>Creature 2</Type>
    <Subtype>Animal Toad 2</Subtype>
    <Attack>3</Attack>
    <Defense>2</Defense>
    <Gems>B2</Gems>
    <Rules/>
    <Flavor>Fire toads have the uncanny ability to cook their food while chewing. 2</Flavor>
    <Rarity>Common 2</Rarity>
    <Series>1</Series>
    <Number>106</Number>
    <Illustrator>Kevin Shoemaker 2</Illustrator>
  </row>
  <row>
    <name>Fire Toad 3</name>
    <id>7366070c-c74d-4bb9-b3d8-23177e887073</id>
    <Sphere>Nature 3</Sphere>
    <Cost>1</Cost>
    <Type>Creature 3</Type>
    <Subtype>Animal Toad 3</Subtype>
    <Attack>3</Attack>
    <Defense>2</Defense>
    <Gems>B3</Gems>
    <Rules/>
    <Flavor>Fire toads have the uncanny ability to cook their food while chewing. 3</Flavor>
    <Rarity>Common 3</Rarity>
    <Series>1</Series>
    <Number>106</Number>
    <Illustrator>Kevin Shoemaker 3</Illustrator>
  </row>
</data>