Import XML with attributes into mysql

Question

I have a large (~30Mb) XML file like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<LIC Version="2.0" Title="Products">
    <Item>
        <Field Name="Filename">/root/_DOWNLOAD/Bird.txt</Field>
        <Field Name="Read_By">Lisa Hannigan</Field>
        <Field Name="Posit">Passenger</Field>
    </Item>
    <Item>
        <Field Name="Filename">D:\03\Strypers.pdf</Field>
        <Field Name="Read_By">Stryper</Field>
        <Field Name="Intensity">2</Field>
        <Field Name="IMG">78</Field>
        <Field Name="Rotate">0</Field>
    </Item>
    <Item>
        <Field Name="Filename">D:\Afriu.txt</Field>
        <Field Name="Read_By">Africano</Field>
        <Field Name="Posit">Canto Africano vol.1</Field>
        <Field Name="File_Resource">mp3</Field>
    </Item>
    <Item>
        <Field Name="Filename">D:\_VARIOUS\Knots.pdf</Field>
        <Field Name="Date">40624</Field>
    </Item>
    ...
</LIC>

I want to import this xml into mysql database, with a php script. I've used SIMPLEXML and xpath:

    $url = 'FILE.xml';
    $xml = simplexml_load_file($url);
    $result = $xml->xpath("//Field[@Name]");
foreach { ... }

What do i need? What is the correct "foreach" to create an array to use for mysql sql? Notes that every row (identify by "Item") is not same (not have the same "Field Name"). Is it correct to use simplexml for larger file? Thank you for help!

update

This is an example to use "foreach", i tried:

$result = $xml->xpath("//Field[@Name]");    
foreach($result as $key => $value)  {
    echo $value['Name']."=".$value.",";
 }

Now I want to find out how to create the string to insert in mysql

I think you may need to explain your MySQL table structure if you said every `Item` may have different `Field.Name`... — Passerby, Jan 09 '13 at 11:54
You can use XPath to read each item, and then use XPath to read each field based on it's name value, i.e. `$item->xpath("Field[@Name='Read_By']")` of which you can then get the value of that field. — Gavin, Jan 09 '13 at 11:55
@Passerby : mySQL table was create with all field! From my example: `CREATE TABLE 'custom' ( `IDm` int(30) NOT NULL AUTO_INCREMENT, `Filename` TEXT, `Read_By` varchar(240), `Posit` varchar(255), `Intensity` varchar(240), `IMG` TEXT, `Rotate` varchar(240), `File_Resource` varchar(240), `Date` varchar(240),` and so on.. — kingmo, Jan 09 '13 at 12:38
See as well: [How to import XML file into MySQL database table using XML_LOAD(); function](http://stackoverflow.com/q/5491056/367456) — hakre, Mar 28 '15 at 20:33

score 3 · Answer 1 · answered Jan 09 '13 at 12:14

3

First create a table that matches all possible fields as columns. Then you can load it by a LOAD XML LOCAL INFILE query.

LOAD XML LOCAL INFILE 'file.xml'
  INTO TABLE person
  ROWS IDENTIFIED BY '<Item>';

answered Jan 09 '13 at 12:14

Shiplu Mokaddim

56,364
17
141
187

Thanks, but I already tried this query and the result is a table with all fields = NULL – kingmo Jan 09 '13 at 12:41
@kingmo: Watch out that you properly align the attribute names with your column names. Read the manual link carefully. – hakre Jan 09 '13 at 22:23
CAn I use this with php - I cannot make it happen via php, even though I can import the file into the database with direct access via terminal, I just want to import it via web – deckoff Dec 13 '13 at 20:21
Yes you can do it by php, provided the user you are using to access database in php has FILE privilege. – Shiplu Mokaddim Dec 15 '13 at 02:49

score 1 · Accepted Answer · answered Jan 11 '13 at 17:23

I try to answer my question.

<?php
    $url = 'FILEXML';
    $xml = simplexml_load_file($url);    
$i = 1;
  foreach($xml->xpath("/LIC/Item") as $docs)
  {
        foreach($docs->Field as $field) 
        {
            $resultstr[] = $field["Name"];
        }
    $sql_head = headquote($resultstr);
    $sql_ins = "INSERT INTO table_name (";
    $sql_dec = ") VALUE (";
    unset($resultstr);
    $fresult = (array)$docs;
    $fvalue = array_pop($fresult);
    $sql_val = numking($fvalue);
    $sql_end = ");";
    $query_to_use_for_mysql = ($sql_ins.$sql_head.$sql_dec.$sql_val.$sql_end);

    unset($fresult);
    unset($fvalue);
 }
 ?>

And add this two functions:

<?php
    function headquote($hdarray) {
            $hdata   = array();
            foreach ( $hdarray as $hdval ) {
                #       Use backticks instead quotes!
            $hdata[] = "`$hdval`";
              }
        $hdarray = implode($hdata, ',');
        return $hdarray;
     }
    function numking($input) {
            $data   = array();
            foreach ( $input as $value ) {
            $data[] = is_numeric($value) ? $value : "'".mysql_escape_string($value)."'";
             }
            $input = implode($data, ',');
            return $input;
      }
?>

Thanks to all for help!

Daya · Answer 3 · 2013-01-09T12:38:29.230

0

$url = 'FILE.xml';
$xml = simplexml_load_file($url);
for($i=0;$i<count($xml->Item);$i++)
{
   print_r($xml->Item[$i]);
 }

edited Jan 09 '13 at 12:38

answered Jan 09 '13 at 12:06

Daya

1,170
10
22

Import XML with attributes into mysql

3 Answers3

Linked

Related