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

For parsing XML data, use DOM: php.net/manual/en/book.dom.php — Pekka
– Pekka, Commented Oct 17, 2010 at 16:28
For inserting data into a database, PDO is arguably the best choice nowadays: php.net/manual/en//book.pdo.php — Pekka
– Pekka, Commented Oct 17, 2010 at 16:29
@Pekka for MySQL the best choice is mysqli (if you dont need the abstraction PDO provides) — Gordon
– Gordon, Commented Oct 17, 2010 at 17:44

jsalonen · Accepted Answer · 2010-10-17 16:34:34Z

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(...); //
}

Ansar Muhammad · Accepted Answer · 2010-10-17 17:24:55Z

1

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') );
  }  
}

answered Oct 17, 2010 at 17:24

Ansar Muhammad

1,2765 gold badges26 silver badges45 bronze badges

2 Comments

Gordon Over a year ago

Using DOMDocument::loadXML will create an E_STRICT notice.

Ansar Muhammad Over a year ago

yes I saw it.. and changed the code to $xmlDoc = new DOMDocument(); $xmlDoc->loadXML( $xmlString );

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related