通过php将不规则xml导入mysql

pkwftd7m 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(255)

我希望你能帮忙。我有一个要导入mysql表的xml文件。不幸的是，它不是一个标准的xml文件。格式见下表：

<?xml version="1.0" encoding="UTF-8"?>
<DDCCommonData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" source="ECloudConnect" AuditDate="2018-11-30T13:11:32.4222315Z">
<CounterNotification>
<Date>2018-11-29T01:48:00</Date>
<MachineModel>Model 123</MachineModel>
<MachineID xsi:nil="true" />
<SerialNumber>CGD67291</SerialNumber>
<SourceDeviceID xsi:nil="true" />
<SourceModelID xsi:nil="true" />
<EncompassID xsi:nil="true" />
<ChargeCounter>
  <Counter Mode="PRINT" Color="BLACK" Type="COPY">
    <Large>0</Large>
    <Small>2043</Small>
  </Counter>
  <Counter Mode="PRINT" Color="FULL" Type="COPY">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="PRINT" Color="TWIN" Type="COPY">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="PRINT" Color="BLACK" Type="FAX">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="PRINT" Color="FULL" Type="FAX">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="PRINT" Color="TWIN" Type="FAX">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="PRINT" Color="BLACK" Type="PRINT">
    <Large>0</Large>
    <Small>2470</Small>
  </Counter>
  <Counter Mode="PRINT" Color="FULL" Type="PRINT">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="PRINT" Color="TWIN" Type="PRINT">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="PRINT" Color="BLACK" Type="LIST">
    <Large>0</Large>
    <Small>11</Small>
  </Counter>
  <Counter Mode="PRINT" Color="FULL" Type="LIST">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="PRINT" Color="TWIN" Type="LIST">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="SCAN" Color="BLACK" Type="COPY">
    <Large>0</Large>
    <Small>1270</Small>
  </Counter>
  <Counter Mode="SCAN" Color="FULL" Type="COPY">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="SCAN" Color="TWIN" Type="COPY">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="SCAN" Color="BLACK" Type="FAX">
    <Large>0</Large>
    <Small>21</Small>
  </Counter>
  <Counter Mode="SCAN" Color="FULL" Type="FAX">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="SCAN" Color="TWIN" Type="FAX">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="SCAN" Color="BLACK" Type="NET">
    <Large>1</Large>
    <Small>544</Small>
  </Counter>
  <Counter Mode="SCAN" Color="FULL" Type="NET">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
  <Counter Mode="SCAN" Color="TWIN" Type="NET">
    <Large>0</Large>
    <Small>0</Small>
  </Counter>
</ChargeCounter>
<ErrorHistory xsi:nil="true" />
<DepartmentCodes />

我需要导入以下数据：
机器模型序列号
这些都是简单的，但是计费器的信息就变得复杂了。我需要每个计数器模式的所有大小数据：
大-小
这是我用来导入machinemodel和serialnumber的，我只需要每个chargercounter的大小计数器。

<?php
$conn = mysqli_connect("localhost", "root", "test", "info");

$affectedRow = 0;

$xml = simplexml_load_file("input.xml") or die("Error: Cannot create object");

foreach ($xml->children() as $row) {
$MachineModel = $row->MachineModel ;
$SerialNumber= $row->SerialNumber;

$sql = "INSERT INTO counts(MachineModel ,SerialNumber) VALUES ('" . $MachineModel . "','" . $SerialNumber. "')";

$result = mysqli_query($conn, $sql);

if (! empty($result)) {
    $affectedRow ++;
} else {
    $error_message = mysqli_error($conn) . "\n";
}
}
?>

更新

我需要输出以适应此表：

CREATE TABLE info (
id int(9) NOT NULL auto_increment,

modelname varchar(255) NULL,
serialnumber varchar(255) NULL,

print_black_copy_sm varchar(255) NULL,
print_black_copy_lg varchar(255) NULL,
print_full_copy_sm varchar(255) NULL,
print_full_copy_lg varchar(255) NULL,
print_twin_copy_sm varchar(255) NULL,
print_twin_copy_lg varchar(255) NULL,

print_black_fax_sm varchar(255) NULL,
print_black_fax_lg varchar(255) NULL,
print_full_fax_sm varchar(255) NULL,
print_full_fax_lg varchar(255) NULL,
print_twin_fax_sm varchar(255) NULL,
print_twin_fax_lg varchar(255) NULL,

print_black_print_sm varchar(255) NULL,
print_black_print_lg varchar(255) NULL,
print_full_print_sm varchar(255) NULL,
print_full_print_lg varchar(255) NULL,
print_twin_print_sm varchar(255) NULL,
print_twin_print_lg varchar(255) NULL,

print_black_list_sm varchar(255) NULL,
print_black_list_lg varchar(255) NULL,
print_full_list_sm varchar(255) NULL,
print_full_list_lg varchar(255) NULL,
print_twin_list_sm varchar(255) NULL,
print_twin_list_lg varchar(255) NULL,

scan_black_copy_sm varchar(255) NULL,
scan_black_copy_lg varchar(255) NULL,
scan_full_copy_sm varchar(255) NULL,
scan_full_copy_lg varchar(255) NULL,
scan_twin_copy_sm varchar(255) NULL,
scan_twin_copy_lg varchar(255) NULL,

scan_black_fax_sm varchar(255) NULL,
scan_black_fax_lg varchar(255) NULL,
scan_full_fax_sm varchar(255) NULL,
scan_full_fax_lg varchar(255) NULL,
scan_twin_fax_sm varchar(255) NULL,
scan_twin_fax_lg varchar(255) NULL,

scan_black_net_sm varchar(255) NULL,
scan_black_net_lg varchar(255) NULL,
scan_full_net_sm varchar(255) NULL,
scan_full_net_lg varchar(255) NULL,
scan_twin_net_sm varchar(255) NULL,
scan_twin_net_lg varchar(255) NULL,

PRIMARY KEY (id)
) ENGINE=InnoDB;

mysql php

来源：https://stackoverflow.com/questions/53562047/import-irregular-xml-into-mysql-via-php

2条答案

按热度按时间

wqsoz72f1#

您的表结构设计得非常糟糕，不建议您这样存储重复数据，相反，我建议您使用以下方法。。。

CREATE TABLE counts (
    id int(9) NOT NULL auto_increment,
    modelname varchar(255) NULL,
    serialnumber varchar(255) NULL,
    counter_color varchar(255) NULL,
    counter_type varchar(255) NULL,
    counter_small varchar(255) NULL,
    counter_large varchar(255) NULL
    PRIMARY KEY (id)
) ENGINE=InnoDB;

然后，您可以读取每个计数器元素，并使用颜色和类型属性，将其直接存储到表中，而不是有一个巨大的表。。。

$xml = simplexml_load_file("NewFile.xml") or die("Error: Cannot create object");

$sql = "INSERT INTO counts(MachineModel ,SerialNumber, counter_color, counter_type
            counter_small, counter_large) 
    VALUES (?,?,?,?,?,?)";
$stmt = mysqli_prepare($conn, $sql) or die(mysqli_error($conn));

foreach ($xml->CounterNotification as $row) {
    $MachineModel = $row->MachineModel ;
    $SerialNumber= $row->SerialNumber;

    echo $MachineModel."/".$SerialNumber.PHP_EOL;
    foreach ( $row->ChargeCounter->Counter as $counter )    {
        mysqli_stmt_bind_param($stmt, "ssssss", $MachineModel, $SerialNumber,
            $counter['Color'], $counter['Type'], $counter->Small, $counter->Large);
        $result = mysqli_execute($stmt);
        if (! empty($result)) {
            $affectedRow ++;
        } else {
            $error_message = mysqli_error($conn) . "\n";
        }
    }
}

赞(0）回复(0）举报 2021-06-18

wlp8pajw2#

这里有一个黑客。它只是基本上完成了xml文件。

<?php
$conn = mysqli_connect("localhost", "root", "test", "info");

$affectedRow = 0;

$file_contents = sprintf("%s</CounterNotification> </DDCCommonData>",file_get_contents("input.xml"));

$xml = simplexml_load_string($file_contents) or die("Error: Cannot create object");

foreach ($xml->children() as $row) {
    $MachineModel = $row->MachineModel ;
    $SerialNumber= $row->SerialNumber;

    $sql = "INSERT INTO counts(MachineModel ,SerialNumber) VALUES ('" . $MachineModel . "','" . $SerialNumber. "')";

    $result = mysqli_query($conn, $sql);

    if (! empty($result)) {
        $affectedRow ++;
    } else {
        $error_message = mysqli_error($conn) . "\n";
    }
}

赞(0）回复(0）举报 2021-06-18

我来回答

通过php将不规则xml导入mysql

2条答案

相关问题

热门标签

最新问答