Import XML files into MySQL

I am interested in importing various XML files into a MySQL database.

Anyone here have experience with this?

 

If it's not in some form of mysql usable format of xml, such as: https://dev.mysql.com/doc/refman/5.5/en/load-xml.html , I would write a script to parse it out and insert what you need. You could write a script in most languages very easily as most have built in methods to deal with xml.

 

The simplest way is to parse the XML and build a query.
But this works only if the file is fairly small in size,
else either the script will keep timing out,
or you won't have enough memory to load the entire XML file in a variable for parsing,
or you'll keep losing connection to mysql.

If the file is very large,
use a php script to cut it into smaller chunks,
then run the query script on individual files...

You can cut it in chunks by reading file line by line,
and writing 100-200-500 items in a new file,
ultimately ending up with ,3,4 etc smaller files.

This will be very difficult if XML file is like this:
<item><title>some title</title><description /></item><item><title>some title</title><description /></item><item><title>some title</title><description /></item><item><title>some title</title><description /></item><item><title>some title</title><description /></item>

But very easy if its like this:
<item>
<title>some title</title>
<description />
</item>
<item>
<title>some title</title>
<description />
</item>