xml - Powershell - 将复杂的 XML 转换为 CSV

Question

我想将以下 XML 转换为 CSV。虽然它在 XML 文件中有标题行信息。

<?xml version="1.0" encoding="UTF-8"?>
<myfile>
  <updatetime>2019-07-30 08:30:30</updatetime>
  <process code="PRS1234" name="PROCESS1234" />
  <equipment code="EQP1234" name="EQUIPMENT1234" />
  <product type="equipment" planned="300" time="36000" cycletime="20" />
  <shift code="1" timestart="2019-07-30 02:00:00">
    <order index="1" goodproduct="500" defectproduct="5" time="2019-07-30 02:00:00" />
    <order index="2" goodproduct="980" defectproduct="7" time="2019-07-30 03:00:00" />
    <order index="3" goodproduct="1200" defectproduct="12" time="2019-07-30 04:00:00" />
    <order index="4" goodproduct="1800" defectproduct="15" time="2019-07-30 05:00:00" />
    <order index="5" goodproduct="2500" defectproduct="15" time="2019-07-30 06:00:00" />
  <shift>
  <shift code="2" timestart="2019-07-30 07:00:00">
    <order index="1" goodproduct="600" defectproduct="5" time="2019-07-30 07:00:00" />
    <order index="2" goodproduct="980" defectproduct="7" time="2019-07-30 08:00:00" />
    <order index="3" goodproduct="1500" defectproduct="8" time="2019-07-30 09:00:00" />
    <order index="4" goodproduct="1700" defectproduct="11" time="2019-07-30 10:00:00" />
    <order index="5" goodproduct="3000" defectproduct="15" time="2019-07-30 11:00:00" />
  </shift>
</myfile>

我可以获得所需节点的值。这就是我所做的。

[xml]$inputFile = Get-Content "Q:\XML\FileComplex.xml"
$inputFile.myfile.product | Select-Object -Property type,planned,time,cycletime | ConvertTo-Csv -NoTypeInformation -Delimiter ";" | Set-Content -Path "Q:\XML\FileComplex.csv" -Encoding UTF8 "

我想要实现的是将所有需要的信息组合在一起，并将其作为 CSV 文件的一个记录，就像这样

updatetime          | code(process) | name(process) | code(equipment) | name(equipment) | type(product) | planned(product) | time(product) | cycletime(product) | goodproduct(shift(code) is 1 and index is max) | defectproduct(shift(code) is 1 and index is max) | goodproduct(shift(code) is 2 and index is max) | defectproduct((shift(code) is 2 where index is max)
2019-07-30 08:30:30 | PRS1234       | PROCESS1234   | EQP1234         | EQUIPMENT1234   | equipment     | 300              | 36000         | 20                 |                               2500             |                                               15 |                                           3000 |                                       15

我真的很感谢你的支持!!

提前致谢
娜塔莎

Answer 1

我会使用 SelectSingleNode() 解决这个问题和 XPath。

$data = New-Object xml;
$data.load(".\myfile.xml")

$record = [pscustomobject]@{
    "updatetime"             = $data.SelectSingleNode("/*/updatetime")."#text"
    "code(process)"          = $data.SelectSingleNode("/*/process").code
    "name(process)"          = $data.SelectSingleNode("/*/process").name
    "code(equipment)"        = $data.SelectSingleNode("/*/equipment").code
    "name(equipment)"        = $data.SelectSingleNode("/*/equipment").name
    "type(product)"          = $data.SelectSingleNode("/*/product").type
    "planned(product)"       = $data.SelectSingleNode("/*/product").planned
    "time(product)"          = $data.SelectSingleNode("/*/product").time
    "cycletime(product)"     = $data.SelectSingleNode("/*/product").cycletime
    "goodproduct(shift 1)"   = $data.SelectSingleNode("/*/shift[@code = 1]/order[not(@index < ../order/@index)]").goodproduct
    "defectproduct(shift 1)" = $data.SelectSingleNode("/*/shift[@code = 1]/order[not(@index < ../order/@index)]").defectproduct
    "goodproduct(shift 2)"   = $data.SelectSingleNode("/*/shift[@code = 2]/order[not(@index < ../order/@index)]").goodproduct
    "defectproduct(shift 2)" = $data.SelectSingleNode("/*/shift[@code = 2]/order[not(@index < ../order/@index)]").defectproduct
}

$record
#$record | ConvertTo-Csv -NoTypeInformation

order[not(@index < ../order/@index)]的说明:“任何 <order>，其 index 不小于其旁边的任何其他 index 的 <order>。” - 唯一 <order>满足此条件的是具有最大值 index 的条件。 .

输出是这样的(在转换为CSV之前)

更新时间:2019-07-30 08:30:30
代码(进程):PRS1234
名称(进程):PROCESS1234
代码(设备):EQP1234
名称(设备):EQUIPMENT1234
类型(产品):设备
计划(产品):300
时间(产品):36000
循环时间(产品):20
goodproduct(shift 1) : 2500
缺陷产品(类次 1):15
好产品(类次 2):3000
缺陷产品(类次 2):15