xml - Powershell-将XML转换为CSV

我可以使用以下代码将XML转换为CSV:

    #read from file
[xml]$inputFile = Get-Content "c:\pstest\test.xml"
#export xml as csv
$inputFile.Transaction.ChildNodes | Export-Csv "c:\pstest\test.csv" -NoTypeInformation -Delimiter:";" -Encoding:UTF8

如果文件仅包含一个根节点和一种类型的子节点，则可以使用该文件，例如:

<?xml version="1.0" encoding="UTF-8"?>
<Transaction>
    <TXNDETAIL>
        <RecordID>02</RecordID>
        <SequenceNumber>1</SequenceNumber>
        <TransactionType>01</TransactionType>
        <ActionCode>01</ActionCode>
        <TransactionID>17500515552017001</TransactionID>
        <SellerCode>2200919TRY</SellerCode>
        <BuyerCode>KOCZER</BuyerCode>
        <TransactionReference> </TransactionReference>
        <TransactionDescription1> </TransactionDescription1>
        <TransactionDescription2> </TransactionDescription2>
        <DocumentType>01</DocumentType>
        <DocumentNumber>XXXXXXXXXXX</DocumentNumber>
        <DocumentDate>20170301</DocumentDate>
        <DocumentAmount>10000</DocumentAmount>
        <CurrencyCode>949</CurrencyCode>
        <TransactionAmount>10000</TransactionAmount>
        <TransactionDueDate>20170505</TransactionDueDate>
        <AdditionalInformation1> </AdditionalInformation1>
        <AdditionalInformation2> </AdditionalInformation2>
        <HashCode>XXXXXXXX</HashCode>
    </TXNDETAIL>
    <TXNDETAIL>
        <RecordID>02</RecordID>
        <SequenceNumber>2</SequenceNumber>
        <TransactionType>01</TransactionType>
        <ActionCode>01</ActionCode>
        <TransactionID>17500515622017001</TransactionID>
        <SellerCode>2200919TRY</SellerCode>
        <BuyerCode>KOCZER</BuyerCode>
        <TransactionReference> </TransactionReference>
        <TransactionDescription1> </TransactionDescription1>
        <TransactionDescription2> </TransactionDescription2>
        <DocumentType>01</DocumentType>
        <DocumentNumber>XXXXXXXXXXX</DocumentNumber>
        <DocumentDate>20170301</DocumentDate>
        <DocumentAmount>10000</DocumentAmount>
        <CurrencyCode>949</CurrencyCode>
        <TransactionAmount>10000</TransactionAmount>
        <TransactionDueDate>20170505</TransactionDueDate>
        <AdditionalInformation1> </AdditionalInformation1>
        <AdditionalInformation2> </AdditionalInformation2>
        <HashCode>XXXXXXXX</HashCode>
    </TXNDETAIL>
    <TXNDETAIL>
        <RecordID>02</RecordID>
        <SequenceNumber>3</SequenceNumber>
        <TransactionType>01</TransactionType>
        <ActionCode>01</ActionCode>
        <TransactionID>17500515972017001</TransactionID>
        <SellerCode>2200919TRY</SellerCode>
        <BuyerCode>KOCZER</BuyerCode>
        <TransactionReference> </TransactionReference>
        <TransactionDescription1> </TransactionDescription1>
        <TransactionDescription2> </TransactionDescription2>
        <DocumentType>01</DocumentType>
        <DocumentNumber>XXXXXXXXXXX</DocumentNumber>
        <DocumentDate>20170301</DocumentDate>
        <DocumentAmount>10000</DocumentAmount>
        <CurrencyCode>949</CurrencyCode>
        <TransactionAmount>10000</TransactionAmount>
        <TransactionDueDate>20170505</TransactionDueDate>
        <AdditionalInformation1> </AdditionalInformation1>
        <AdditionalInformation2> </AdditionalInformation2>
        <HashCode>XXXXXXXX</HashCode>
    </TXNDETAIL>
</Transaction>

输出如下:

    "RecordID";"SequenceNumber";"TransactionType";"ActionCode";"TransactionID";"SellerCode";"BuyerCode";"TransactionReference";"TransactionDescription1";"TransactionDescription2";"DocumentType";"DocumentNumber";"DocumentDate";"DocumentAmount";"CurrencyCode";"TransactionAmount";"TransactionDueDate";"AdditionalInformation1";"AdditionalInformation2";"HashCode"
"02";"1";"01";"01";"17500515552017001";"2200919TRY";"KOCZER";"";"";"";"01";"XXXXXXXXXXX";"20170301";"10000";"949";"10000";"20170505";"";"";"XXXXXXXX"
"02";"2";"01";"01";"17500515622017001";"2200919TRY";"KOCZER";"";"";"";"01";"XXXXXXXXXXX";"20170301";"10000";"949";"10000";"20170505";"";"";"XXXXXXXX"
"02";"3";"01";"01";"17500515972017001";"2200919TRY";"KOCZER";"";"";"";"01";"XXXXXXXXXXX";"20170301";"10000";"949";"10000";"20170505";"";"";"XXXXXXXX"

太好了

但是，实际上输入文件具有“标题行”信息，即TXNHEAD标签

    <?xml version="1.0" encoding="UTF-8"?>
<Transaction>
    <TXNHEAD>
        <RecordID>01</RecordID>
        <FileName>001</FileName>
        <IntermediaryCode>19000033</IntermediaryCode>
        <ActualizationDate>20170314</ActualizationDate>
        <SequenceNumber>001</SequenceNumber>
        <NumberofRecords>3</NumberofRecords>
        <AmountofRecords>30000</AmountofRecords>
    </TXNHEAD>
    <TXNDETAIL>
        <RecordID>02</RecordID>
        <SequenceNumber>1</SequenceNumber>
        <TransactionType>01</TransactionType>
        <ActionCode>01</ActionCode>
        <TransactionID>17500515552017001</TransactionID>
        <SellerCode>2200919TRY</SellerCode>
        <BuyerCode>KOCZER</BuyerCode>
        <TransactionReference> </TransactionReference>
        <TransactionDescription1> </TransactionDescription1>
        <TransactionDescription2> </TransactionDescription2>
        <DocumentType>01</DocumentType>
        <DocumentNumber>XXXXXXXXXXX</DocumentNumber>
        <DocumentDate>20170301</DocumentDate>
        <DocumentAmount>10000</DocumentAmount>
        <CurrencyCode>949</CurrencyCode>
        <TransactionAmount>10000</TransactionAmount>
        <TransactionDueDate>20170505</TransactionDueDate>
        <AdditionalInformation1> </AdditionalInformation1>
        <AdditionalInformation2> </AdditionalInformation2>
        <HashCode>XXXXXXXX</HashCode>
    </TXNDETAIL>
    <TXNDETAIL>
        <RecordID>02</RecordID>
        <SequenceNumber>2</SequenceNumber>
        <TransactionType>01</TransactionType>
        <ActionCode>01</ActionCode>
        <TransactionID>17500515622017001</TransactionID>
        <SellerCode>2200919TRY</SellerCode>
        <BuyerCode>KOCZER</BuyerCode>
        <TransactionReference> </TransactionReference>
        <TransactionDescription1> </TransactionDescription1>
        <TransactionDescription2> </TransactionDescription2>
        <DocumentType>01</DocumentType>
        <DocumentNumber>XXXXXXXXXXX</DocumentNumber>
        <DocumentDate>20170301</DocumentDate>
        <DocumentAmount>10000</DocumentAmount>
        <CurrencyCode>949</CurrencyCode>
        <TransactionAmount>10000</TransactionAmount>
        <TransactionDueDate>20170505</TransactionDueDate>
        <AdditionalInformation1> </AdditionalInformation1>
        <AdditionalInformation2> </AdditionalInformation2>
        <HashCode>XXXXXXXX</HashCode>
    </TXNDETAIL>
    <TXNDETAIL>
        <RecordID>02</RecordID>
        <SequenceNumber>3</SequenceNumber>
        <TransactionType>01</TransactionType>
        <ActionCode>01</ActionCode>
        <TransactionID>17500515972017001</TransactionID>
        <SellerCode>2200919TRY</SellerCode>
        <BuyerCode>KOCZER</BuyerCode>
        <TransactionReference> </TransactionReference>
        <TransactionDescription1> </TransactionDescription1>
        <TransactionDescription2> </TransactionDescription2>
        <DocumentType>01</DocumentType>
        <DocumentNumber>XXXXXXXXXXX</DocumentNumber>
        <DocumentDate>20170301</DocumentDate>
        <DocumentAmount>10000</DocumentAmount>
        <CurrencyCode>949</CurrencyCode>
        <TransactionAmount>10000</TransactionAmount>
        <TransactionDueDate>20170505</TransactionDueDate>
        <AdditionalInformation1> </AdditionalInformation1>
        <AdditionalInformation2> </AdditionalInformation2>
        <HashCode>XXXXXXXX</HashCode>
    </TXNDETAIL>
</Transaction>

当应用相同的代码时，我得到:

    "RecordID";"FileName";"IntermediaryCode";"ActualizationDate";"SequenceNumber";"NumberofRecords";"AmountofRecords"
"01";"001";"19000033";"20170314";"001";"3";"30000"
"02";;;;"1";;
"02";;;;"2";;
"02";;;;"3";;

当我尝试使用此代码来仅检索头部时:

#read from file
[xml]$inputFile = Get-Content "c:\pstest\test.xml"
#export xml as csv
$inputFile.Transaction.TXNHEAD.ChildNodes | Export-Csv "c:\pstest\test.csv" -NoTypeInformation -Delimiter:";" -Encoding:UTF8

我得到:

"#text"
"01"
"001"
"19000033"
"20170314"
"001"
"3"
"30000"

我想要实现的是以下输出:

"RecordID";"FileName";"IntermediaryCode";"ActualizationDate";"SequenceNumber";"NumberofRecords";"AmountofRecords"
"01";"001";"19000033";"20170314";"001";"3";"30000"
"RecordID";"SequenceNumber";"TransactionType";"ActionCode";"TransactionID";"SellerCode";"BuyerCode";"TransactionReference";"TransactionDescription1";"TransactionDescription2";"DocumentType";"DocumentNumber";"DocumentDate";"DocumentAmount";"CurrencyCode";"TransactionAmount";"TransactionDueDate";"AdditionalInformation1";"AdditionalInformation2";"HashCode"
"02";"1";"01";"01";"17500515552017001";"2200919TRY";"KOCZER";"";"";"";"01";"XXXXXXXXXXX";"20170301";"10000";"949";"10000";"20170505";"";"";"XXXXXXXX"
"02";"2";"01";"01";"17500515622017001";"2200919TRY";"KOCZER";"";"";"";"01";"XXXXXXXXXXX";"20170301";"10000";"949";"10000";"20170505";"";"";"XXXXXXXX"
"02";"3";"01";"01";"17500515972017001";"2200919TRY";"KOCZER";"";"";"";"01";"XXXXXXXXXXX";"20170301";"10000";"949";"10000";"20170505";"";"";"XXXXXXXX"

我究竟做错了什么？

最佳答案

管道中的第一个对象(或Select-Object等)定义了输出 header ，无论是文件输出还是控制台输出。

您可以做的是将它们分两轮转换为csv，然后将其添加到同一文件中。例如:

$inputFile.Transaction.TXNHEAD | ConvertTo-Csv -NoTypeInformation -Delimiter ";" | Set-Content -Path "c:\pstest\test.csv" -Encoding UTF8
$inputFile.Transaction.TXNDETAIL | ConvertTo-Csv -NoTypeInformation -Delimiter ";" | Add-Content -Path "c:\pstest\test.csv" -Encoding UTF8

您也可以像这样组合它们:

$inputFile.Transaction.TXNHEAD, $x.Transaction.TXNDETAIL |
ForEach-Object { $_ | ConvertTo-Csv -NoTypeInformation -Delimiter ";" } |
Set-Content -Path "c:\pstest\test.csv" -Encoding UTF8