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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.