XML feed to MySQL insert

[EM @MAJ.com]

I search google but no success, not giving up yet!

I need to know best practice to auto-upload the xml feed I received from partners to my MySQL database.

Everyday, I received this XML file e.g. 'testdata.xml'. I add these xml values to the table manually everyday, I need to automate this process.
<?xml version="1.0" encoding="utf-8"?>
<elements>
<device>0-123456</device>
<unixTime>1034268516</unixTime>
<location>N</location>
<data length="9" source="pc" encoding="hex">0xC0560D72DA4AB2445A</data>
</elements>

I need to create a php script to open all xml files (1testdata.xml, 2testdata.xml, 3testdata.xml, etc)and insert these to my Mysql database table using the same field name .e.g device, unixtime, location, and data.

My initial assessment is to create a php script, to read the xml file, capture the xml data, then insert to my MySQL table. And set up a cron to run the script every hour.

Creating the php script to read the xml file, inserting the data to MySQL is where I'm stuck. Any idea where to begin?

Thank you,
em @ king.net

 

[katherine]

You need an XML parser. PHP has XML functions for this purpose.
But it would better if you get the data in CSV format instead, then you can do bulk insert in mySQL using LOAD DATA INFILE (fast and easy). I prefer CSV but it's just me.

Here's a tutorial:
http://fuelyourcoding.com/reading-xml-with-php/

Basically you need a parser to read the file in a loop, extract the relevant tags and perform an insert for each line.
A slightly different approach is to parse the XML file, output the fields to a CSV file, then perform a bulk insert of that intermediate CSV file into mySQL using LOAD DATA INFILE. Faster than inserting line by line and less overhead.
Hope that makes sense

 

[EM @MAJ.com]

Hi Katherine,

Thanks again for your input. I print the tutorial, and will read it.
Cheers.