MySQL Cookbook

Importing XML into MySQL

Problem

You want to import an XML document into a MySQL table.

Solution

Set up an XML parser to read the document. Then use the records in the document to construct and execute INSERT statements.

Discussion

Importing an XML document depends on being able to parse the document and extract record contents from it. The way you do this will depend on how the document is written. For example, one format might represent column names and values as attributes of <column> elements:

<?xml version="1.0" encoding="UTF-8"?>
<rowset>
  <row>
   <column name="subject" value="Jane" />
   <column name="test" value="A" />
   <column name="score" value="47" />
  </row>
  <row>
   <column name="subject" value="Jane" />
   <column name="test" value="B />
   <column name="score" value="50" />
  </row>
...
</rowset>

Another format is to use column names as element names and column values as the contents of those elements:

<?xml version="1.0" encoding="UTF-8"?>
<rowset>
  <row>
   <subject>Jane</subject>
   <test>A</test>
   <score>47</score>
  </row>
  <row>
   <subject>Jane</subject>
   <test>B</test>
   <score>50</score>
  </row>
...
</rowset>

Due to the various structuring possibilities, it’s necessary to make some assumptions about the format you expect the XML document to have. For the example here, I’ll assume the second format just shown. One way to process this kind of document is to use the XML::XPath module, which allows you to refer to elements within the document using path expressions. For example, the path //row selects ...