XML to MYSQL

January 21, 2008 9:19 AM

Is there a simple way to convert an XML feed into a MYSQL database?

Navicat works great, but I'd like something that runs runs on the server, rather than my PC. I've seen the old thread asking the same question, but it did not provide any working solutions. Thanks for the help!

posted by alse to Computers & Internet (6 answers total)

You could use XSLT to transform the XML into SQL statements that you can run on the mysql server.
posted by zsazsa at 9:25 AM on January 21, 2008

Best answer: "What's an XML Feed?", that's the question. XML can have completely arbitrary levels of nesting and complexity which isn't necessarily easy to map to a row-and-column database.

Assuming that your data is the sort that's amenable to being put into a database, (perhaps you mean an RSS feed?) zsazsa's suggestion would work.

A cron job chops the data up into "insert" statements, saves them into .sql files, cron job runs the files at regular intervals, something like that?

Otherwise, you're looking at some kind of script, in Perl or PHP most likely.

Happy to try and create a script.
posted by AmbroseChapel at 7:39 PM on January 21, 2008

Response by poster: I'm looking to parse a feed such as this. I hoped to find something more automatic like Navicat, but it looks like I'll have to learn to program it myself.
posted by alse at 8:16 AM on January 22, 2008

Yikes, that's not even really xml, the good stuff is inside a CDATA. If all you want is to put the "Volcanic", etc data and the link into a database it's a one-liner in any scripting language.
posted by Skorgu at 8:23 AM on January 22, 2008

Best answer: Very brittle example:
curl http://www.google.com/trends/hottrends/atom/hourly | perl -nle 'while(m/class="(\S+)".*href="(.*)"/g){print "INSERT INTO foo \(bar, baz\) VALUES \( \"$1\" \"$2\"\);"}'
posted by Skorgu at 8:38 AM on January 22, 2008 [1 favorite]

Basically the perl script you want goes something like

use LWP::Simple;
use DBI;
$dbh = DBI->connect("DBI:mysql:database=<foo>",<username>,<password>);
$page = get('<url>');
$sth = $dbh->prepare("insert into <table> values ?,?,?");
while($page =~ /<regular expression which grabs 3 things>/g){
    $sth->execute($1,$2,$3);
}
# strict, warnings, add "or die" messages, exercise for the reader etc.

How are you going to handle duplicates?
posted by AmbroseChapel at 6:31 PM on January 22, 2008