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

2022-01-15 00:00:00 xml sql import mariadb 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>

相关文章