import xml file into mysql database using php
26,408
Solution 1
$strContents = file_get_contents($file);
$strDatas = Xml2Array($strContents);
$strEmployeeData = $strDatas['employee']['row'];
$strTableColumn = count($strEmployeeData[0]);
function for Xml2Array:
function Xml2Array($contents, $get_attributes=1, $priority = 'tag') {
if(!$contents) return array();
if(!function_exists('xml_parser_create')) {
//print "'xml_parser_create()' function not found!";
return array();
}
//Get the XML parser of PHP - PHP must have this module for the parser to work
$parser = xml_parser_create('');
xml_parser_set_option($parser, XML_OPTION_TARGET_ENCODING, "UTF-8"); # http://minutillo.com/steve/weblog/2004/6/17/php-xml-and-character-encodings-a-tale-of-sadness-rage-and-data-loss
xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, 0);
xml_parser_set_option($parser, XML_OPTION_SKIP_WHITE, 1);
xml_parse_into_struct($parser, trim($contents), $xml_values);
xml_parser_free($parser);
if(!$xml_values) return;//Hmm...
//Initializations
$xml_array = array();
$parents = array();
$opened_tags = array();
$arr = array();
$current = &$xml_array; //Refference
//Go through the tags.
$repeated_tag_index = array();//Multiple tags with same name will be turned into an array
foreach($xml_values as $data) {
unset($attributes,$value);//Remove existing values, or there will be trouble
//This command will extract these variables into the foreach scope
// tag(string), type(string), level(int), attributes(array).
extract($data);//We could use the array by itself, but this cooler.
$result = array();
$attributes_data = array();
if(isset($value)) {
if($priority == 'tag') $result = $value;
else $result['value'] = $value; //Put the value in a assoc array if we are in the 'Attribute' mode
}
//Set the attributes too.
if(isset($attributes) and $get_attributes) {
foreach($attributes as $attr => $val) {
if($priority == 'tag') $attributes_data[$attr] = $val;
else $result['attr'][$attr] = $val; //Set all the attributes in a array called 'attr'
}
}
//See tag status and do the needed.
if($type == "open") {//The starting of the tag '<tag>'
$parent[$level-1] = &$current;
if(!is_array($current) or (!in_array($tag, array_keys($current)))) { //Insert New tag
$current[$tag] = $result;
if($attributes_data) $current[$tag. '_attr'] = $attributes_data;
$repeated_tag_index[$tag.'_'.$level] = 1;
$current = &$current[$tag];
} else { //There was another element with the same tag name
if(isset($current[$tag][0])) {//If there is a 0th element it is already an array
$current[$tag][$repeated_tag_index[$tag.'_'.$level]] = $result;
$repeated_tag_index[$tag.'_'.$level]++;
} else {//This section will make the value an array if multiple tags with the same name appear together
$current[$tag] = array($current[$tag],$result);//This will combine the existing item and the new item together to make an array
$repeated_tag_index[$tag.'_'.$level] = 2;
if(isset($current[$tag.'_attr'])) { //The attribute of the last(0th) tag must be moved as well
$current[$tag]['0_attr'] = $current[$tag.'_attr'];
unset($current[$tag.'_attr']);
}
}
$last_item_index = $repeated_tag_index[$tag.'_'.$level]-1;
$current = &$current[$tag][$last_item_index];
}
} elseif($type == "complete") { //Tags that ends in 1 line '<tag />'
//See if the key is already taken.
if(!isset($current[$tag])) { //New Key
$current[$tag] = $result;
$repeated_tag_index[$tag.'_'.$level] = 1;
if($priority == 'tag' and $attributes_data) $current[$tag. '_attr'] = $attributes_data;
} else { //If taken, put all things inside a list(array)
if(isset($current[$tag][0]) and is_array($current[$tag])) {//If it is already an array...
// ...push the new element into that array.
$current[$tag][$repeated_tag_index[$tag.'_'.$level]] = $result;
if($priority == 'tag' and $get_attributes and $attributes_data) {
$current[$tag][$repeated_tag_index[$tag.'_'.$level] . '_attr'] = $attributes_data;
}
$repeated_tag_index[$tag.'_'.$level]++;
} else { //If it is not an array...
$current[$tag] = array($current[$tag],$result); //...Make it an array using using the existing value and the new value
$repeated_tag_index[$tag.'_'.$level] = 1;
if($priority == 'tag' and $get_attributes) {
if(isset($current[$tag.'_attr'])) { //The attribute of the last(0th) tag must be moved as well
$current[$tag]['0_attr'] = $current[$tag.'_attr'];
unset($current[$tag.'_attr']);
}
if($attributes_data) {
$current[$tag][$repeated_tag_index[$tag.'_'.$level] . '_attr'] = $attributes_data;
}
}
$repeated_tag_index[$tag.'_'.$level]++; //0 and 1 index is already taken
}
}
} elseif($type == 'close') { //End of tag '</tag>'
$current = &$parent[$level-1];
}
}
return($xml_array);
}
Solution 2
$string = <<<XML
<?xml version="1.0" encoding="utf-8"?>
<title name="employee_details">
<Item>
<field name="employee_name">Hari</field>
<field name="employee_code">101</field>
<field name="employee_email">[email protected]</field>
<field name="employee_designation">Trainee</field>
<field name="employee_salary">6000</field>
</Item>
<Item>
<field name="employee_name">Syed</field>
<field name="employee_code">102</field>
<field name="employee_email">[email protected]</field>
<field name="employee_designation">Trainee</field>
<field name="employee_salary">6000</field>
</Item>
</title>
XML;
$xml = simplexml_load_string($string);
print_r($xml);
From the array you can insert in to mysql table.For mysql data insertion with php you can check this link (http://www.w3schools.com/php/php_mysql_insert.asp)
Solution 3
You can execute mysql query to import data from XML file
LOAD XML LOCAL INFILE 'employee.xml' INTO TABLE employee;
Click here for more details
Author by
hari krishnan
Updated on August 28, 2020Comments
-
hari krishnan over 3 years
the following data is my xml file. how to import it in mysql database using php
<?xml version="1.0" encoding="utf-8"?> <title name="employee_details"> <Item> <field name="employee_name">Hari</field> <field name="employee_code">101</field> <field name="employee_email">[email protected]</field> <field name="employee_designation">Trainee</field> <field name="employee_salary">6000</field> </Item> <Item> <field name="employee_name">Syed</field> <field name="employee_code">102</field> <field name="employee_email">[email protected]</field> <field name="employee_designation">Trainee</field> <field name="employee_salary">6000</field> </Item> <Item> <field name="employee_name">Raja</field> <field name="employee_code">103</field> <field name="employee_email">[email protected]</field> <field name="employee_designation">Trainee</field> <field name="employee_salary">6000</field> </Item> <Item> <field name="employee_name">Murali</field> <field name="employee_code">104</field> <field name="employee_email">[email protected]</field> <field name="employee_designation">Trainee</field> <field name="employee_salary">6000</field> </Item> </title>please help me to import it.