从XML捕获数据并保存在数据库中

Question

I am working on XML file, which i need to read through PHP and then save that data in a database. So far i was successful. The problem is that, it is made specifically for the XML below. What i need, is a generic code which could do the same but for any kind of XML code.

Here is my XML and PHP code

<?xml version="1.0" encoding="ISO-8859-1"?>

<list>

<person>

      <person_id>1</person_id>
      <fname>Mikael</fname>
      <lname>Ronstrom</lname>
  </person>
  <person>
      <person_id>2</person_id>
      <fname>Lars</fname>
      <lname>Thalmann</lname>
  </person>
   <person>
      <person_id>3</person_id>
      <fname>Mikael</fname>
      <lname>Ronstrom</lname>
  </person>
  <person>
      <person_id>4</person_id>
      <fname>Lars</fname>
      <lname>Thalmann</lname>
  </person>
   <person>
      <person_id>5</person_id>
      <fname>Mikael</fname>
      <lname>Ronstrom</lname>
  </person>
  <person>
      <person_id>6</person_id>
      <fname>Lars</fname>
      <lname>Thalmann</lname>

  </person>
 </list> 

here is my Php code

$con = mysql_connect("localhost","root","vertrigo");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("test", $con);



$xml = simplexml_load_file("xml.xml");


$aa = "";
foreach($xml->children() as $child)
{


foreach($child->children() as $childs)
{
 $aa .= "'".$childs ."',";


}
$aa = substr_replace($aa, "", -1);

$sql = "INSERT into tbl_xmldata (person_id,first_name,last_name) values ( $aa )";
$aa = "";
$rr = mysql_query($sql);

} 
if ($rr){ echo "data successfully captured from XML and inserted in db";}else{ echo "sorry no data insertion";}

Answer 1

Update:

Searching on the internet, I found this class that maybe can help you:

It is called MySQL to XML - XML to MySQL and inserts XML in MySQL and exports MySQL to XML

Link: http://www.phpclasses.org/package/782-PHP-Insert-XML-in-MySQL-and-export-MySQL-to-XML.html

---

First idea:

As someone said in the comments of your question:

"A database has a predefined structure - tables, columns, field types,etc., so to import "any" kind of XML means that you're going to have to build some advanced functions that can build the database structures on the fly so that your PHP script can properly insert the data into the right places within that database"

And he's right, It's nearly impossible. My suggestion is to use a class for parsing the XML. This class converts the XML into an $array so then you can get any element you want for your mysql insert (I think is the best you can do).

Download PHP class "Clean XML to array" here:

http://dl.dropbox.com/u/15208254/stackoverflow/lib.xml.php

Example:

<?php 

header('Content-type: text/plain'); 

include('lib.xml.php'); 

$xml = new Xml; 


// PARSE 


// Parse from a source into a variable (default source type : FILE) 
$source = '<?xml version="1.0" encoding="utf-8"?> 

<test> 
  <this a="b"> 
    <is c="d"> 
      <a e="f" g="h"> first test</a> 
    </is> 
    <is> 
      <b hello="world">second test</b> 
    </is> 
  </this> 
</test>'; 
$out = $xml->parse($source, NULL); 
print_r($out); 

/* will output 
Array 
( 
    [this] => Array 
        ( 
            [is] => Array 
                ( 
                    [0] => Array 
                        ( 
                            [a] =>  first test 
                            [a-ATTR] => Array 
                                ( 
                                    [e] => f 
                                    [g] => h 
                                ) 
                        ) 
                    [1] => Array 
                        ( 
                            [b] => second test 
                            [b-ATTR] => Array 
                                ( 
                                    [hello] => world 
                                ) 
                        ) 
                ) 
            [is-ATTR] => Array 
                ( 
                    [c] => d 
                ) 
        ) 
    [this-ATTR] => Array 
        ( 
            [a] => b 
        ) 
) 
*/ 


// Parse from a local file 
$out = $xml->parse('myfile.xml', 'FILE'); 
print_r($out); 

// Parse from a Web file 
$out = $xml->parse('http://site.tld/myfile.xml', 'CURL'); 
print_r($out); 



// ENCODING 

// You could specify an encoding type (default : utf-8) 
$out = $xml->parse('myfile.xml', 'FILE', 'ISO-8859-15'); 
print_r($out); 

// have fun ;-) 

?>

How to insert the values?

Do a for each for the elements that repeats, otherwise to access other elements just do this:

<?php
$array = array(
    "foo" => "bar",
    42    => 24,
    "multi" => array(
         "dimensional" => array(
             "array" => "foo"
         )
    )
);

/* Values are:
* $array[42] --> outputs 24
* $array['foo'] --> outputs bar
* $array['multi']['dimensional']['array'] --> outputs foo
*/
$sql = "INSERT INTO table (id, value1, value2) VALUES ($array[42], '$array['foo']', '$array['multi']['dimensional']['array']')";

?>