CODEWITHSUNDEEP
phpmysqlxml
kingmo
kingmo

Reputation: 133

Import XML with attributes into mysql

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

Upvotes: 2

Views: 7126

Answers (3)

kingmo
kingmo

Reputation: 133

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!

Upvotes: 1

Daya
Daya

Reputation: 1170

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

Upvotes: 0

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57690

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

Upvotes: 3

Related Questions