php - 通过 PHP 将不规则 XML 导入 MySQL
问题描述
我希望你能帮忙。我有一个要导入 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 />
我需要导入以下数据:
机器型号序列号
哪些是简单的,但 ChargeCounter 信息变得复杂。我需要每种计数器模式的所有大数据和小数据:
大小
这就是我用来导入 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;
解决方案
这是一个黑客。它只是基本上完成了 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";
}
}
推荐阅读
- ssl - Firefox 浏览器中使用哪些函数来生成用于 SSL/TLS 会话密钥的随机数?
- firebase - 我的应用程序已连接到我的 firebase 存储,但未连接到 firebase 数据库
- asp.net-core - 如何在 Razer 页面.Net Core 3.1 中正确刷新 PartialView
- python - Django:默认情况下如何停止给予新用户员工状态和超级用户状态
- jsp - 如何从一个jsp页面到下一个jsp页面地址栏检索下拉列表值
- angular - 如何根据 Firebase 服务取消测试 Angular 组件
- c# - 使用 Moq 进行 Elasticsearch Nest 单元测试:无法模拟具体类
- flutter - 弃用自定义颤振小部件
- react-hook-form - 反应钩子形式:点击清除文件输入字段
- hololens - 有没有办法通过 Hololens 在 Unity 中离线使用 Python?