将具有属性的XML导入mysql [英] Import XML with attributes into mysql

问题描述

我有一个大的(〜30Mb)XML文件，如下所示:

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>

我想使用php脚本将此xml导入mysql数据库.我用过SIMPLEXML和xpath:

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 { ... }

我需要什么?什么是创建要用于mysql sql的数组的正确"foreach"? 请注意，每行(由项目"标识)都不相同(没有相同的字段名称"). 对更大的文件使用simplexml是否正确? 谢谢您的帮助！

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!

更新

这是使用"foreach"的示例，我尝试过:

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

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

现在，我想了解如何创建要在mysql中插入的字符串

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

推荐答案

我尝试回答我的问题.

<?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);
 }
 ?>

并添加以下两个功能:

<?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;
      }
?>

感谢大家的帮助！

这篇关于将具有属性的XML导入mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！