如何在我们的数据库中导入 XML 数据?(PHP)

How can I import XML data in our database? (PHP)(如何在我们的数据库中导入 XML 数据?(PHP))

问题描述

我正在尝试将 XML 数据导入我们的数据库 (mariadb).我对php很陌生.

I'm trying to import XML data into our database(mariadb). I'm very new to php.

我们的 XML 结构:

Our XML structure:

    <?xml version="1.0" encoding="UTF-8"?>
     <webexport>
       <article key="98112" status="active">
         <productattributes>
           <group1>
             <feature key="number">
               <en name="Number" value="98112"></en>
               <fr name="Nombre" value="98112"></fr>
               <ger name="Nummer" value="98112"></ger>
            </feature>             
            <feature key="description">
               <en name="Item description" value="VKK-12-8m-11"></en>
               <fr name="Désignation" value="VKK-12-8m-11"></fr>
               <ger name="Artikelbezeichnung" value="VKK-12-8m-11"></ger>
             </feature>
           </group1>
         </productattributes>
       </article>
     </webexport>

这是我想在我们的数据库中拥有的:

This is what I want to have in our database:

+----+---------------+-----------+------+--------------------+--------------+
| id | articleid_des | articleid | lang | description_des    | description  |
+----+---------------+-----------+------+--------------------+--------------+
|  1 | Number        |     98112 | en   | Item description   | VKK-12-8m-11 |
|  2 | Nombre        |     98112 | fr   | Désignation        | VKK-12-8m-11 |
|  3 | Nummer        |     98112 | de   | Artikelbezeichnung | VKK-12-8m-11 |
+----+---------------+-----------+------+--------------------+--------------+

这是我当前的函数，我正在尝试获取属性并将其放入我的变量($en_des、$en_val)中.但在那之后我不知道如何在我们的数据库结构中正确获取这些变量.

This is my current function, I'm trying to get the attributes and put it in my variables ($en_des, $en_val). But after that I don't know how to get these variables correctly in our database structure.

<?php

$xml=simplexml_load_file("exported.xml");

foreach($xml->children() as $article) {
  foreach($article->children() as $productattr) {
    foreach($productattr->children() as $group) {
      foreach($group->children() as $feature) {
        foreach($feature->children() as $en) {
          $en_des=$en['name'];
          $en_val=$en['value'];
          echo $en_des;
          echo "
";
          echo $en_val;
          echo "
";
        }
      }
    }
  }
}

这是我的函数的输出:

编号98112名词98112编号98112商品描述VKK-12-8m-11指定VKK-12-8m-11ArtikelbezeichnungVKK-12-8m-11

推荐答案

考虑 LOADXML 在 MySQL 和 MariaDB 中可用，需要 XSLT，一种特殊用途，由于所需的结构，SQL 等声明性语言用于转换 XML 文件:

Consider LOAD XML available in MySQL and MariaDB which requires XSLT, a special-purpose, declarative language like SQL used to transform XML files due to the needed structure:

<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>

作为一种通用语言，PHP 可以同时运行 XSLT 和SQL 命令:

Being a general-purpose language, PHP can run both XSLT and SQL commands:

XSLT (另存为 .xsl 文件 - 特殊的 .xml 文件)

使用Meunchian方法按<en>、<fr>、<ger>标签分组.

Uses the Meunchian Method to group by <en>, <fr>, <ger> tags.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">
  <xsl:output method="xml" indent="yes"/>
  <xsl:strip-space elements="*"/>

  <xsl:key name="lang_key" match="feature/*" use="name()"/>

  <xsl:template match="/webexport">
      <xsl:apply-templates select="article"/>
  </xsl:template>

  <xsl:template match="article|productattributes|group1"> 
      <xsl:apply-templates select="*"/>
  </xsl:template>

  <xsl:template match="feature[position()=1]"> 
    <table>
      <xsl:for-each select="*[count(. | key('lang_key', name())[1]) = 1]">
          <xsl:variable select="name()" name="curr_key"/>
          <row>
              <id><xsl:value-of select="position()"/></id>
              <articleid_des><xsl:value-of select=".[name()=$curr_key]/@name"/></articleid_des>
              <articleid><xsl:value-of select=".[name()=$curr_key]/@value"/></articleid>
              <lang><xsl:value-of select="$curr_key"/></lang>
              <description_des><xsl:value-of select="../following-sibling::feature/*[name()=$curr_key]/@name"/></description_des>
              <description><xsl:value-of select="../following-sibling::feature/*[name()=$curr_key]/@value"/></description>
          </row>      
      </xsl:for-each>
    </table>
  </xsl:template>

</xsl:stylesheet>

XSLT 演示

PHP (不需要 foreach 循环或 if 逻辑)

PHP (no foreach loops or if logic needed)

// IMPORT XML
$xml = new DOMDocument('1.0', 'UTF-8');
$xml->load('/path/to/Input.xml');

// IMPORT XSLT 
$xsl = new DOMDocument('1.0', 'UTF-8');   
$xsl->load('/path/to/XSLT_Script.xsl');

// INITIALIZE TRANSFORMER
$proc = new XSLTProcessor;
$proc->importStyleSheet($xsl);

// TRANSFORM SOURCE
$newXML = $proc->transformToDoc($xml);

// SAVE TO FILE
file_put_contents('/path/to/Output.xml', $newXML);

// RUN MARIADB COMMAND (MAY NEED TO ALLOW --local-infile IN SETTINGS)
try {
   $conn = new mysqli($servername, $username, $password, $dbname);
   $conn->query("LOAD XML DATA INFILE '/path/to/Output.xml'
                 INTO TABLE myFinalTable
                 ROWS IDENTIFIED BY '<row>';");
} catch(Exception $e) {  
    echo $e->getMessage();  
}

$conn->close();

XML输出(用于数据库导入)

<?xml version="1.0" encoding="UTF-8"?>
<table>
   <row>
      <id>1</id>
      <articleid_des>Number</articleid_des>
      <articleid>98112</articleid>
      <lang>en</lang>
      <description_des>Item description</description_des>
      <description>VKK-12-8m-11</description>
   </row>
   <row>
      <id>2</id>
      <articleid_des>Nombre</articleid_des>
      <articleid>98112</articleid>
      <lang>fr</lang>
      <description_des>Désignation</description_des>
      <description>VKK-12-8m-11</description>
   </row>
   <row>
      <id>3</id>
      <articleid_des>Nummer</articleid_des>
      <articleid>98112</articleid>
      <lang>ger</lang>
      <description_des>Artikelbezeichnung</description_des>
      <description>VKK-12-8m-11</description>
   </row>
</table>

这篇关于如何在我们的数据库中导入 XML 数据?(PHP)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！