Looking for a way to parse and insert XML string into MySQL using php

Question

I am looking for a way to parse and insert following XML string data to MySQL tables using php.. please help

<?xml version="1.0" encoding="UTF-8"?><brandModelListResponse>  
<brand ID="Nokia" Description="Nokia">  
    <model ID="N93i" OS="Symbian" OSVersion="SymS60V3" image="nokia-n93i.gif">N93i</model>  
    <model ID="N95" OS="Symbian" OSVersion="SymS60V3" image="nokia-n95.gif">N95</model>  
    <model ID="Nokia300" OS="Symbian" OSVersion="SymS60V3" image="">Nokia300</model>  
</brand>
<brand ID="Motorola" Description="Motorola">  
    <model ID="E1070" OS="J2ME" OSVersion="Motorola J2ME A.5" image="'E1070.jpg">E1070</model>  
    <model ID="E398" OS="J2ME" OSVersion="Motorola J2ME A.1" image="">E398</model>  
</brand>  

I have two tables, one for brands and other models.. need to insert all the data available in above xml to these tables..

Thanks in advance
Ansar

Answer 1

In order to collect the data from XML and save it into a database, two steps needs to be performed. First, you need to read the XML and store it into some PHP data structures. Secondly, you need to store these data structures into a database.

Assuming you are using a decently fresh PHP (>=5) you just:

• Read the XML data using SimpleXML or some other XML parser (like DOM)
• Store data to a MySQL database using either PHP's build-in MySQL support or newer PDO library

So begin with reading the data with an XML parser. And once you have done that, continue with a MySQL library. In order to store data to a MySQL database, some rudimentary SQL knowledge is needed. Consult for instance W3School's MySQL tutorial.

Of course, you need to do the final coding yourself. But just as a simple structural proposal, your script might look something like this:

$xml_data = file_get_contents('file.xml');
$xml = new SimpleXMLElement($xml_data); 

foreach ($xml->item as $item)
{
  $sql = 'INSERT INTO table (...) VALUES ('.$item['name'].');';
  mysql_query(...); //
}

Answer 2

Thanks for all replies.. I did it using php DOM

$xmlDoc = DOMDocument::loadXML( $xmlString );
//Get all brands 
$brand = $xmlDoc->getElementsByTagName('brand');

for($i=0; $i <= $brand->length-1; $i++)
{
  //get each brands data and insert to MySQL
  $brandid = insertBrands($brand->item($i)->getAttribute('ID'), $brand->item($i)->getAttribute('Description') );

  //get models for this brand
  $model = $brand->item($i)->getElementsByTagName('model');
  for($j=0; $j <= $model->length-1; $j++)
  {    
    //get models data and insert to MySQL
    insertModles($brandid,  $model->item($j)->getAttribute('ID'), $model->item($j)->getAttribute('OS')
      , $model->item($j)->getAttribute('OSVersion'), $model->item($j)->getAttribute('image') );
  }  
}