How can the Stack Overflow data in XML be converted into a MySQL dump?

It will be brilliant to have it for mysqlimport.


I made a program to create a SQL file to load SO data into a PostgreSQL database. Reading it, it seems there are few PostgreSQL-specific things in it so you may try to port it.

| improve this answer | |

If I'd use PHP for the import, I wouldn't use preg_match_all but XMLReader.
The XML files all have a similar structure. Each element <row> stands for one record and the attributes correspond to a field of that record. Each attribute is present in all records. Now all you have to do is to create tables with field names that are exactly like the attribute names in the XML files and write a simple script that puts the value of an attribute in the table field with the same name.

First attempt, without error/schema handling, almost untested:

    $pdo = new PDO('mysql:host=???;dbname=???', '???', '???');
    // I'm using a Gentoo-patched version of PHP -> default connection charset=utf8
    // You might have to take care of that.

    foreach(array('users','posts','comments','votes', 'badges') as $source) {
        echo $source, "\n";
        $reader = new XMLReader;
        $reader->open($source.'.xml') or die('!open '.$f);
        $bFirst = true;
        while ($reader->read()) {
            if ( XMLREADER::ELEMENT===$reader->nodeType && 'row'===$reader->localName) {
                if ($bFirst) {
                    $bFirst = false;
                    $params = array();
                    $stmt = createStmt($pdo, $reader, $source, $params);
                do {
                    $params[$reader->name] = $reader->value;
                } while ( $reader->moveToNextAttribute() );
    function createStmt($pdo, $reader, $table, &$bindArr) {
        do {
            $fields[] = $reader->name;
        } while ( $reader->moveToNextAttribute() );
        $query = sprintf('INSERT INTO %s (%s) VALUES(:%s)', $table, join(',',$fields), join(',:',$fields));
        echo 'query: ', $query, "\n";
        $stmt = $pdo->prepare($query);
        foreach($fields as $f) {
            $bindArr[$f] = '';
            $stmt->bindParam(':'.$f, $bindArr[$f]);
        return $stmt;

With the MySQL Connector/Net and System.Xml.XmlReader it should be possible to do almost exactly the same thing with C#/.NET.

I wouldn't put too much effort in error handling, schema testing, "but what if", etc. here. If you need something beyond "just fiddling with the data" there are other tools; you could even use Hibernate for that ;-)

| improve this answer | |
  • VolkerK: How long does it take for your code to write all the files to the database? I ask because I'm writing something in C# using the MySQL Connector/Net and System.Xml.XmlReader as you mention at the bottom of your post, but this takes hours to run (about 10 hours writing one statement at a time, and somewhere between 3 and 4 i think when writing 500 per query - e.g. Insert into table values (rec1), (rec2), (rec3). ). I'd like this to be faster, but don't have a baseline for normal times. I've got a question on this site regarding fast ways to do this, but haven't gotten much of a response – AgentConundrum Sep 24 '09 at 7:30
  • I just let the php script run and it took <20 minutes to import the "Sep 09" data dump on my BenQ S73G notebook (T2400, 1GB ddr2, all on one hdd). Windows XP, PHP 5.3.0, MySQL 5.1.37, MyISAM, no indices. And though this is only a dumb import I think you should be able to beat both the execution time ...and the hardware ;-) the votes table took the longest (8 minutes), followed by posts (7 minutes) – VolkerK Sep 25 '09 at 0:49

Just took a look at the data.

It wouldn't be too hard to do a pattern matching preg_match_all using PHP to get all the data and then print it out into SQL statements. (if someone hasn't already done it)

| improve this answer | |
  • 4
    PHP has lots of XML parsers, preg_match_all is out of discussion in my opinion. – Ionuț G. Stan Jun 15 '09 at 4:18
  • true true, i unfortunately am an addict for regular expression :P (not necessarlly a good thing :P) – Shadi Almosri Jun 15 '09 at 9:03

You must log in to answer this question.