如何在数据库中导入XML数据？ （PHP）

Question

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

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 |
+----+---------------+-----------+------+--------------------+--------------+

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

This is the output of my function:

Number 98112 Nombre 98112 Nummer 98112 Item description VKK-12-8m-11 Désignation VKK-12-8m-11 Artikelbezeichnung VKK-12-8m-11

Answer 1

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>

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

XSLT (save as .xsl file -a special .xml file)

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 Demo

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 Output (used for database import)

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