再會，
我是 powershell 的新手，我正在嘗試將復雜的 xml 決議為 CSV：
這是xml代碼
<LoyaltyCustomer Action="E">
<Retailer Id="1">
<HouseHold BuyingUnitInternalKey="2" HouseHoldExternalId="-1" SendEmail="false">
<Members>
<Member MemberInternalKey="2" MemberExternalId="-1" IsMainMember="true" LastName="Internal" FirstName="Use" StartDate="2012-10-02T12:42:00" RedemptionPrivileges="0" MemberStatus="0" AdressNormalizationUpdate="N">
<Cards>
<Card Id="-1" CardStatus="" IssueDate="2012-10-02T12:42:00" ExpirationDate="2056-12-31T00:00:00" />
</Cards>
<Stores>
<Store Id="26" StoreTypeId="1" IsHomeStore="true" />
</Stores>
</Member>
</Members>
</HouseHold>
<HouseHold BuyingUnitInternalKey="3" HouseHoldExternalId="244003000001" Country="11" State="223" City="Calgary" Street1="Main St" StreetNum="203" PostalCode="R4C 3R1" POBox="999999" HomePhone="438-439-1246" EMailAddress="[email protected]" SendEmail="true">
<Accounts>
<Account Id="1" EarnValue="6422.0000" RedeemValue="6049.0000" Balance="373.0000" LastUpdate="2013-05-10" HousekeepingBalance="0.0000" HousekeepingTotalAccumulated="0.0000" HousekeepingTotalRedeemed="0.0000" />
<Account Id="3" EarnValue="10.0000" RedeemValue="8.0000" Balance="2.0000" LastUpdate="2013-02-20" HousekeepingBalance="0.0000" HousekeepingTotalAccumulated="0.0000" HousekeepingTotalRedeemed="0.0000" />
<Account Id="4" EarnValue="10.0000" RedeemValue="7.0000" Balance="3.0000" LastUpdate="2013-02-20" HousekeepingBalance="0.0000" HousekeepingTotalAccumulated="0.0000" HousekeepingTotalRedeemed="0.0000" />
<Account Id="5" EarnValue="8.0000" RedeemValue="5.0000" Balance="3.0000" LastUpdate="2013-04-18" HousekeepingBalance="0.0000" HousekeepingTotalAccumulated="0.0000" HousekeepingTotalRedeemed="0.0000" />
<Account Id="6" EarnValue="9.0000" RedeemValue="8.0000" Balance="1.0000" LastUpdate="2013-02-20" HousekeepingBalance="0.0000" HousekeepingTotalAccumulated="0.0000" HousekeepingTotalRedeemed="0.0000" />
<Account Id="7" EarnValue="7028.0000" RedeemValue="6500.0000" Balance="528.0000" LastUpdate="2017-07-10" HousekeepingBalance="0.0000" HousekeepingTotalAccumulated="0.0000" HousekeepingTotalRedeemed="0.0000" />
<Account Id="8" EarnValue="269319.0000" RedeemValue="269000.0000" Balance="319.0000" LastUpdate="2019-07-10" HousekeepingBalance="0.0000" HousekeepingTotalAccumulated="0.0000" HousekeepingTotalRedeemed="0.0000" />
<Account Id="10" EarnValue="688968.0000" RedeemValue="682641.0000" Balance="6327.0000" LastUpdate="2019-07-10" HousekeepingBalance="0.0000" HousekeepingTotalAccumulated="0.0000" HousekeepingTotalRedeemed="0.0000" />
</Accounts>
<HouseHoldSegments>
<Segment Id="100" Status="1" AttachmentSourceId="1" />
<Segment Id="500" Status="1" AttachmentSourceId="4" />
<Segment Id="502" Status="1" AttachmentSourceId="8" />
<Segment Id="531" Status="1" AttachmentSourceId="1" />
</HouseHoldSegments>
<Members>
<Member MemberInternalKey="3" MemberExternalId="244003000001" IsMainMember="true" LastName="zabana" FirstName="Mike" BirthDate="1970-11-04" DriversLicense="drvlic" NationalInsuranceNumber="socsec" Remarks="Test Account" MobilePhoneNumber="438-439-1246" Gender="1" Title="1" StartDate="2013-01-21T14:28:00" EffectiveDate="2013-02-08T13:58:00" RedemptionPrivileges="0" LanguageId="0" NumberOfFamilyMembers="4" Anonimity="0" MemberStatus="1" ReceiptLayoutId="1" AdressNormalizationUpdate="N" UpdatedDate="2019-07-20T14:47:00" CommercialDriversLicense="comdrvlic">
<Cards>
<Card Id="244003000001" CardStatus="1" IssueDate="2013-01-21T00:00:00" ExpirationDate="2056-12-31T23:59:00" />
</Cards>
<Stores>
<Store Id="24" StoreTypeId="1" IsHomeStore="true" />
</Stores>
<MemberAttributes>
<Attribute Id="10004" Value="zabana MANAGEMENTS" />
</MemberAttributes>
<MemberAdditionalAddress />
<FamilyMembers>
<FamilyMember FamilyMemberId="1" TypeId="2" Name="Kathy" />
<FamilyMember FamilyMemberId="2" TypeId="1" Name="Melissa" BirthdayDate="1997-06-06" Gender="2" />
<FamilyMember FamilyMemberId="3" TypeId="1" Name="Trent" BirthdayDate="2000-08-29" Gender="1" />
</FamilyMembers>
</Member>
<Member MemberInternalKey="23612" MemberExternalId="244003000002" IsMainMember="false" LastName="zabana" FirstName="Kathy" BirthDate="1970-07-02" Remarks="Test Account for Family testing" Gender="2" Title="3" StartDate="2013-03-11T13:16:00" RedemptionPrivileges="0" LanguageId="0" PostOption="1" NumberOfFamilyMembers="1" Anonimity="0" MemberStatus="1" AdressNormalizationUpdate="N" UpdatedDate="2019-07-15T19:44:00">
</Member>
<Member MemberInternalKey="33421" MemberExternalId="244003000003" IsMainMember="false" LastName="zabana" FirstName="Trent" BirthDate="2000-08-29" Remarks="Test account to see how a different address and email affect the member export" Gender="1" Title="1" StartDate="2013-03-27T14:41:00" EffectiveDate="2017-06-26T13:55:00" RedemptionPrivileges="3" LanguageId="0" PostOption="1" NumberOfFamilyMembers="1" Anonimity="0" MemberStatus="1" AdressNormalizationUpdate="N" UpdatedDate="2019-07-05T18:07:00">
</Member>
</Members>
</HouseHold>
</Retailer>
</LoyaltyCustomer>
我想要得到的是這個輸出：
HouseHoldExternalId IsMainMember MemberExternalId FirstName LastName StartDate Id Balance
------------------- ------------ ---------------- --------- -------- -------------- -- -------
-1 TRUE -1 Use Internal 10/2/2012 12:42
244003000001 TRUE 244003000001 Mike Zabana 1/21/2013 14:28
244003000001 FALSE 244003000002 Kathy zabana 3/11/2013 13:16
244003000001 FALSE 244003000003 Trent zabana 3/27/2013 14:41
244003000001 1 373
244003000001 3 2
244003000001 4 3
244003000001 5 3
244003000001 6 1
244003000001 7 528
244003000001 8 319
244003000001 10 6327
我在 PowerShell 中使用下面的這個腳本：
$data = New-Object xml;
$data.load("C:\temp\parsing\mehdi.xml")
$mehdi = [pscustomobject]@{
"HouseHoldExternalId" = $data.SelectNodes("/LoyaltyCustomer/Retailer/HouseHold").HouseHoldExternalId |Out-String
"MemberExternalId" = $data.SelectNodes("/LoyaltyCustomer/Retailer/HouseHold/Members/Member").MemberExternalId |Out-String
"IsMainMember" = $data.SelectNodes("/LoyaltyCustomer/Retailer/HouseHold/Members/Member").IsMainMember |Out-String
"LastName" = $data.SelectNodes("/LoyaltyCustomer/Retailer/HouseHold/Members/Member").LastName |Out-String
"FirstName" = $data.SelectNodes("/LoyaltyCustomer/Retailer/HouseHold/Members/Member").FirstName |Out-String
"StartDate" = $data.SelectNodes("/LoyaltyCustomer/Retailer/HouseHold/Members/Member").StartDate |Out-String
"Id" = $data.SelectNodes("/LoyaltyCustomer/Retailer/HouseHold/Accounts/Account").Id |Out-String
"Balance" = $data.SelectNodes("/LoyaltyCustomer/Retailer/HouseHold/Accounts/Account").Balance |Out-String }
$mehdi | ConvertTo-Csv -NoTypeInformation -Delimiter ";" | Set-Content -Path C:\temp\parsing\test2.csv -Encoding UTF8
我得到的是所有資料都在 HouseholdExternalID 列下。
我怎樣才能得到相同的輸出？我將感謝您的幫助和建議。謝謝
uj5u.com熱心網友回復：
似乎這應該可以將您的 XML 轉換為object[]您可以在之后匯出為 CSV 的。基本上，您的預期輸出是 3 個陣列的組合：
$xml.LoyaltyCustomer.Retailer.Household獲取HouseHoldExternalId$xml.LoyaltyCustomer.Retailer.Household.Members.Member獲取IsMainMember、MemberExternalId、FirstName、LastName 和 StartDate$xml.LoyaltyCustomer.Retailer.Household.Accounts.Account獲取ID 和余額
$xml = [xml]::new()
$xml.Load("C:\temp\parsing\mehdi.xml")
$psobjectOut = {
param($element, $subelement)
[pscustomobject]@{
HouseHoldExternalId = $element.HouseHoldExternalId
IsMainMember = $subelement.IsMainMember
MemberExternalId = $subelement.MemberExternalId
FirstName = $subelement.FirstName
LastName = $subelement.LastName
StartDate = '{0}' -f $subelement.StartDate -as [datetime]
Id = $subelement.Id
Balance = $subelement.Balance
}
}
$result = foreach($element in $xml.LoyaltyCustomer.Retailer.Household)
{
foreach($subelement in $element.Members.Member)
{
& $psobjectOut -element $element -subelement $subelement
}
foreach($subelement in $element.Accounts.Account)
{
& $psobjectOut -element $element -subelement $subelement
}
}
檢查在$result：
PS /> $result | Format-Table
HouseHoldExternalId IsMainMember MemberExternalId FirstName LastName StartDate Id Balance
------------------- ------------ ---------------- --------- -------- --------- -- -------
-1 true -1 Use Internal 10/2/2012 12:42:00 PM
244003000001 true 244003000001 Mike zabana 1/21/2013 2:28:00 PM
244003000001 false 244003000002 Kathy zabana 3/11/2013 1:16:00 PM
244003000001 false 244003000003 Trent zabana 3/27/2013 2:41:00 PM
244003000001 1 373.0000
244003000001 3 2.0000
244003000001 4 3.0000
244003000001 5 3.0000
244003000001 6 1.0000
244003000001 7 528.0000
244003000001 8 319.0000
244003000001 10 6327.0000
uj5u.com熱心網友回復：
添加到圣地亞哥的好答案，我想使用 xPath 攻擊它。因此，我使用 an 建立了他的作業并提出了以下調整 t
[XML]$XML = [XML]( Get-Content 'C:\Scripts\Example.xml' )
$HouseHolds = Select-Xml -Xml $Xml -XPath '/LoyaltyCustomer/Retailer/HouseHold'
$Output =
For($i = 0; $i -lt $HouseHolds.Count; $i ) {
$HouseHoldID = $HouseHolds[$i].Node.HouseHoldExternalId
$HouseHoldXpath = "/LoyaltyCustomer/Retailer/HouseHold[$($i 1)]"
Select-Xml -Xml $XML -XPath "$HouseHoldXpath/Members/Member|$HouseHoldXpath/Accounts/Account" |
ForEach-Object{
[PSCustomObject]@{
HouseHoldExternalId = $HouseHoldID
IsMainMember = $_.Node.IsMainMember
MemberExternalId = $_.Node.MemberExternalId
FirstName = $_.Node.FirstName
LastName = $_.Node.LastName
StartDate = $_.Node.StartDate -as [DateTime]
Id = $_.Node.Id
Balance = $_.Node.Balance
}
}
}
$Output | Format-Table -AutoSize
回傳：
HouseHoldExternalId IsMainMember MemberExternalId FirstName LastName StartDate Id Balance
------------------- ------------ ---------------- --------- -------- --------- -- -------
-1 true -1 Use Internal 10/2/2012 12:42:00 PM
244003000001 1 373.0000
244003000001 3 2.0000
244003000001 4 3.0000
244003000001 5 3.0000
244003000001 6 1.0000
244003000001 7 528.0000
244003000001 8 319.0000
244003000001 10 6327.0000
244003000001 true 244003000001 Mike zabana 1/21/2013 2:28:00 PM
244003000001 false 244003000002 Kathy zabana 3/11/2013 1:16:00 PM
244003000001 false 244003000003 Trent zabana 3/27/2013 2:41:00 PM
由于組合了 xPath 陳述句，因此只需要 1 個內部回圈。因此，實際上并不需要預先打包一個運算式來輸出 PSCustomObject。也就是說，排序有點偏離，大概是由于 xPath 查詢的運行方式。然而，所有的結果都在那里。
早期沒有使用 xPath 的修訂版具有正確的順序：
[XML]$XML = [XML]( Get-Content 'C:\Scripts\Example.xml' )
$HouseHolds = Select-Xml -Xml $Xml -XPath '/LoyaltyCustomer/Retailer/HouseHold'
$ObjectExpression = {
[PSCustomObject]@{
HouseHoldExternalId = $HouseHoldID
IsMainMember = $_.Node.IsMainMember
MemberExternalId = $_.Node.MemberExternalId
FirstName = $_.Node.FirstName
LastName = $_.Node.LastName
StartDate = $_.Node.StartDate -as [DateTime]
Id = $_.Node.Id
Balance = $_.Node.Balance
}
}
$Output =
For($i = 0; $i -lt $HouseHolds.Count; $i ) {
$HouseHoldID = $HouseHolds[$i].Node.HouseHoldExternalId
$HouseHoldXpath = "{0}{1}" -f $HouseHolds[$i].Pattern, "[$($i 1)]"
Select-Xml -Xml $XML -XPath "$HouseHoldXpath/Members/Member" |
ForEach-Object{ & $ObjectExpression }
Select-Xml -Xml $XML -XPath "$HouseHoldXpath/Accounts/Account" |
ForEach-Object{ & $ObjectExpression }
}
$Output | Format-Table -AutoSize
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/380569.html