Insert XML Data to MySQL Table Using PHP

Question

I am trying to insert xml data to my sql but it is not inserting. how to write the foreach(xml->) for this type of xml.the xml is generated dynamically in this structure.this is a sample xml in this format

<?php
$xmlData =<<< END
<?xml version="1.0"?>
<Customer>
<id>1</id>
<name>Oluwafemi</name>
<address>Cresent Drive, TX</address>
<list>
<contact>56689</contact>
<telephone>5889745</telephone>
</list>
<offer>congrats</offer>
</Customer>
END;

$xml = simplexml_load_string($xmlData) or die("ERROR: Cannot create SimpleXML object");
$connection = mysqli_connect("localhost", "root", "", "Customers") or die ("ERROR: Cannot connect");

foreach ($xml->Customer as $Customer) {
$id = $Customer->id;
echo "$id";
$name =  $Customer->name;
$address = $Customer->address;

$sql = "INSERT INTO customerdata (id, name, address ) VALUES ('$id', '$name', '$address')";
mysqli_query($connection, $sql) or die ("ERROR: " .mysqli_error($connection) . " (query was $sql)");
}
mysqli_close($connection);
?>

Hint: The mysql commandline tool can import most XML files directly. See as well [How to import XML file into MySQL database table using XML_LOAD(); function](http://stackoverflow.com/q/5491056/367456) — hakre, Mar 15 '15 at 13:02
Hntt#2: `$xml` in your example represents the document `` element already. This mistake is common amongst new SimpleXML users. In your case you just don't need to foreach here as it's only a single customer row. — hakre, Mar 15 '15 at 13:07

score 0 · Answer 1 · answered Mar 15 '15 at 05:06

Assuming you have multiple customers...

$sXmlString =<<< END
<?xml version="1.0"?>
<Content>
<Customer>
<id>1</id>
<name>Oluwafemi</name>
<address>Cresent Drive, TX</address>
</Customer>
<Customer>
<id>2</id>
<name>Oluwafemi2</name>
<address>Cresent Drive 2, TX</address>
</Customer>
</Content>
END;

$oXml       = simplexml_load_string($sXmlString);
$sJson      = json_encode( $oXml );
$aContent   = json_decode( $sJson, TRUE );
$aCustomers = $aContent[ 'Customer' ];
var_dump( $aCustomers );
$iCountCustomers = count( $aCustomers );
for( $i = 0; $i < $iCountCustomers; ++$i )
{
    $sId      = $aCustomers[ $i ][ 'id' ];
    $sName    = $aCustomers[ $i ][ 'name' ];
    $sAddress = $aCustomers[ $i ][ 'address' ];
    var_dump( $sId );
    var_dump( $sName );
    var_dump( $sAddress );
}

score 0 · Accepted Answer · 2015-03-15T06:35:44.613

You need to edit your XML document, encapsulate the document with this tag:
<Customers> ... </Customers>

So the beginning portion of your code should look like:

<?xml version="1.0"?>
$sXmlString =<<< END
<Customers>
  <Customer>
    <id>1</id>
    <name>Oluwafemi</name>
    <address>Cresent Drive, TX</address>
  </Customer>
</Customers>
END;

EDIT: OP is unable to edit the XML provided, I assume it is structured where new customers would be added in with additional id, name, address entities. Here is the updated code for OP:

<?php
$xmlData =<<< END
<?xml version="1.0"?>
<Customer>
  <id>1</id>
  <name>Oluwafemi</name>
  <address>Cresent Drive, TX</address>
  <id>2</id>
  <name>Rob</name>
  <address> 123 Longhorn </address>
</Customer>
END;

$xml = simplexml_load_string($xmlData) or die("ERROR: Cannot create SimpleXML object");
$connection = mysqli_connect("localhost", "root", "", "Customers") or die ("ERROR: Cannot connect");

/* Assumes that the number of IDs = number of customers */
$size = sizeOf($xml->id);
$i = 0; //index

/* Add each customer to the database, See how we reference it as    $xml->ENTITY[INDEX] */
while($i != $size) 
{
    //echo $xml->id[$i]; //Test
    $sql = "INSERT INTO customerdata (id, name, address ) VALUES ('$xml->id[$i]', '$xml->name[$i]', '$xml->address[$i]')";
    mysqli_query($connection, $sql) or die ("ERROR: " .mysqli_error($connection) . " (query was $sql)");

    $i++; //increment index
}

mysqli_close($connection);

I also added in an additional customer id with data. You can remove that and the code will still work fine.

Hi RobMullins, the xml is generated dynamically. any help for that xml — user3243931, Mar 15 '15 at 05:51
You should really structure your XML better. Since you cannot, I will update the answer... — , Mar 15 '15 at 06:27
Thanku Rob Mullins. got the expected output what i want. thanku so much — user3243931, Mar 15 '15 at 12:13
if there is one more element inside how can we get the data. — user3243931, Mar 15 '15 at 14:28
Additional entities would be referenced within the while loop, as `$xml->ENTITY[$i]` So if you had a phone-number field, it would be: `$xml->phone[$i]` Call `print_r($xml)` before the while loop so you can better understand the structure. — , Mar 15 '15 at 21:27

Insert XML Data to MySQL Table Using PHP

2 Answers2

Linked