如何在我们的数据库中导入XML数据? (PHP) [英] How can I import XML data in our database? (PHP)
问题描述
我正在尝试将XML数据导入我们的数据库(mariadb).我是php的新手.
I'm trying to import XML data into our database(mariadb). I'm very new to php.
我们的XML结构:
<?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 "\n";
echo $en_val;
echo "\n";
}
}
}
}
}
这是我的函数的输出:
Number
98112
Nombre
98112
Nummer
98112
Item description
VKK-12-8m-11
Désignation
VKK-12-8m-11
Artikelbezeichnung
VKK-12-8m-11
Number
98112
Nombre
98112
Nummer
98112
Item description
VKK-12-8m-11
Désignation
VKK-12-8m-11
Artikelbezeichnung
VKK-12-8m-11
推荐答案
考虑加载MySQL和MariaDB中提供的XML ,它需要 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>
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)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!