Stefan Ernst

Web: Getting XML into MySQL/CSV

Web: Getting XML into MySQL/CSV

I was confronted with the task of getting multiple XML files, each weighing in at more than 25 megabytes into a MySQL database.

Everything I basically had access to to properly convert it was a machine running PHP4/PHP5, MySQL and Apache - also, increasing the annoyance I had to put up with a memory limit of 50mb and a maximum script execution time of 30 seconds. Sounded like a lot of fun to me.

I tried googling, various homemade XML parsers, splitting the files up more or less automatically to get past the limitations of the server - but nothing was exactly proving to be anything like useful in a productive environment, either the server spat at me for consuming to many resources or the parsing routines didn’t even remotely get behind the complexity of the actual XML content with it’s massive amounts of parameters and multilevel entries.

More in an act of despair than anything I stumbled over a useful little function, which, just of the name, I apparently underestimated by far.

simplexml_load_file

It’s native to PHP5 and simply grabs a file you specify and converts it into an Object/Array construct. including multiple levels, all the parameters, everything I needed.

You only need

    <?php
        $xml = simplexml_load_file('Data.xml');
    ?>

to get all your XML data into the $xml Object/Array construct.
Use var_dump($xml) or print_r($xml) or have a quick look at the data and some help to figure out how to use it to create your MySQL queries, CSV or whatever form you would want your data in.

    foreach ($xml as $value) {
        // All your output queries here!
    }

It uses rather low resources and is pretty quick too.

Some things are just too obvious to quickly find them in the first place. ;)

Comments

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <pre>
  • Lines and paragraphs break automatically.

More information about formatting options

CAPTCHA
Are you human?
                                                                            
                                                                            
                           lllllll                       555555555555555555 
                           l:::::l                       5::::::::::::::::5 
                           l:::::l                       5::::::::::::::::5 
                           l:::::l                       5:::::555555555555 
 yyyyyyy           yyyyyyy  l::::l      eeeeeeeeeeee     5:::::5            
  y:::::y         y:::::y   l::::l    ee::::::::::::ee   5:::::5            
   y:::::y       y:::::y    l::::l   e::::::eeeee:::::ee 5:::::5555555555   
    y:::::y     y:::::y     l::::l  e::::::e     e:::::e 5:::::::::::::::5  
     y:::::y   y:::::y      l::::l  e:::::::eeeee::::::e 555555555555:::::5 
      y:::::y y:::::y       l::::l  e:::::::::::::::::e              5:::::5
       y:::::y:::::y        l::::l  e::::::eeeeeeeeeee               5:::::5
        y:::::::::y         l::::l  e:::::::e            5555555     5:::::5
         y:::::::y         l::::::l e::::::::e           5::::::55555::::::5
          y:::::y          l::::::l  e::::::::eeeeeeee    55:::::::::::::55 
         y:::::y           l::::::l   ee:::::::::::::e      55:::::::::55   
        y:::::y            llllllll     eeeeeeeeeeeeee        555555555     
       y:::::y                                                              
      y:::::y                                                               
     y:::::y                                                                
    y:::::y                                                                 
   yyyyyyy
Enter the code depicted in ASCII art style.