我试图构建一个脚本将XML文件转换为CSV。我开始遵循这个指南https://learn.microsoft.com/en-us/answers/questions/542481/parse-xml-to-csv-help.html，但我现在被以下情况所困:
我的代码的第一部分工作得很好，它将第一列写入CSV文件，但是当它经过以下迭代时，即使它将值输出到控制台，它也没有将值写入文件。这是我的代码:
[xml]$x = Get-Content C:UsersDesktopPolicy.xml
$x.Profiles |
ForEach-Object{
$_.Profile |
ForEach-Object{
$_.Ruleset |
ForEach-Object{
Write-Host "Policy ID = " $_.ID
[PSCustomObject]@{
policyName = $_.ID
}
$_.Conditions |
ForEach-Object{
$_.condition |
ForEach-Object{
if($_.name -eq "Resource") {
Write-Host "Resource = " $_.'#text'
[PSCustomObject]@{
resource = $_.'#text'
}
}
if($_.name -eq "Action") {
Write-Host "Action = " $_.'#text'
[PSCustomObject]@{
action = $_.'#text'
}
}
if($_.name -eq "actor") {
Write-Host "actor = " $_.'#text'
[PSCustomObject]@{
actor = $_.'#text'
}
}
if($_.name -eq "child") {
Write-Host "child = " $_.'#text'
[PSCustomObject]@{
child = $_.'#text'
}
}
if($_.name -eq "number") {
Write-Host "number = " $_.'#text'
[PSCustomObject]@{
number = $_.'#text'
}
}
}
}
}
}
} | Export-Csv C:UsersDesktoppolicy.csv -NoTypeInformation
因此，直到第一个[PSCustomObject](第10行)之前，它工作良好，并且policyName列被写入具有相应值的CSV值。但是在第二个[PSCustomObject](第19行)中，它应该写入资源/动作/演员/孩子/号码，它不再写入文件。
如何将这些值添加到已经存在的[PSCustomObject]中?
参考:
<XML片段/strong>
<?xml version="1.0" encoding="UTF-8"?>
<Profiles xmlns:pcrf="nothing interesting here">
<Profile Desc="some description" ID="someid" Prio="0">
<Ruleset Class="class1" Desc="" ID="policyid1" Prio="10" active="true">
<Conditions>
<condition name="Resource" type="matchExact">resource1</condition>
<condition name="Action" type="matchExact">action1</condition>
<condition name="actor" type="matchExact">actor1</condition>
</Conditions>
</Ruleset>
<Ruleset Class="classX" Desc="" ID="policyidX" Prio="10" active="true">
<Conditions>
<condition name="Resource" type="matchExact">resource4</condition>
<condition name="Action" type="matchExact">action4</condition>
<condition name="child" type="matchExact">child1,child2</condition>
</Conditions>
</Ruleset>
</Profile>
<Profile Desc="some description" ID="someid" Prio="0">
<Ruleset Class="classY" Desc="" ID="policyidY" Prio="10" active="true">
<Conditions>
<condition name="Resource" type="matchExact">resource99</condition>
<condition name="Action" type="matchExact">action00</condition>
<condition name="child" type="matchExact">child5</condition>
<condition name="number" type="matchExact">number1</condition>
</Conditions>
</Ruleset>
</Profile>
</Profiles>
我得到这个CSV作为结果:
"policyName"
"policyid1"
这是powershell输出:
PS C:UsersDesktop> .xmltocsv.ps1
Policy ID = policyid1
Resource = resource1
Action = action1
actor = actor1
Resource = resource4
Action = action4
child = child1,child2
Resource = resource99
Action = action00
child = child5
number = number1
这是我期望得到的CSV文件:
"policyName","Resource","Action","actor","child","number"
"policyid1","resource1","action1","actor1","",""
"policyidX","resource4","action4","","child1,child2",""
"policyidY","resource99","action0","","child5","number1"
代码的主要问题是，您需要为每个<condition>元素创建一个输出对象，并使用从父元素及其子元素收集的属性值，而您正在为每个输出对象的属性创建一个单独的对象([pscustomobject]);下面是一个非常精简的解决方案:
$x.Profiles.Profile.Ruleset.Conditions |
ForEach-Object {
$outputObj = [pscustomobject] @{
# Get the policy ID from the parent node.
policyId = $_.ParentNode.ID
# The following properties are filled in below.
Resource = ''
Action = ''
actor = ''
child = ''
number = ''
}
# Loop over all child elements and fill in the property
# that corresponds to the "name" attribute with the element's inner text.
# Note that the "name" *attribute* actually shadows
# the element's type-native .Name *property*.
foreach ($el in $_.ChildNodes) {
$outputObj.($el.name) = $el.InnerText
}
# Output the resulting object
$outputObj
} |
ConvertTo-Csv # To export to a *file, use Export-Csv
这将产生如问题所示的所需输出。
注意:
这将为每个
<Conditions>元素创建一个[pscustomobject]，使用从父元素收集的属性值以及一组固定的属性，如果基于匹配的name属性值在子元素中出现，则使用匹配的子元素的内部文本作为属性值来填充这些属性。注意，属性访问
.Profiles.Profile.Ruleset.Conditions隐式地遍历多个<Profile>和<Ruleset>元素，并返回它们全部，这是一个称为成员访问枚举的特性。
先放入表格，然后另存为CSV
using assembly System
using assembly System.Xml.Linq
$Filename = "c:temptest.xml"
$xDoc = [System.Xml.Linq.XDocument]::Load($Filename)
$ruleSets = $xDoc.Descendants("Ruleset").Foreach([System.Xml.Linq.XElement])
$table = [System.Collections.ArrayList]::new()
foreach($ruleSet in $ruleSets)
{
$newRow = New-Object -TypeName psobject
$id = $ruleSet.Attribute("ID").Value
$newRow | Add-Member -NotePropertyName policyName -NotePropertyValue $id
$conditions = $ruleSet.Descendants("condition").Foreach([System.Xml.Linq.XElement])
foreach($condition in $conditions)
{
$name = $condition.Attribute("name").Value
$value = $condition.Value
$newRow | Add-Member -NotePropertyName $name -NotePropertyValue $value
}
$table.Add($newRow) | Out-Null
}
$table | format-Table
不是为每个列创建一个[PSCustomObject]，而是创建一个单个[PSCustomObject]用于整个行。你可以这样做，首先创建一个有序的哈希表[ordered] @{}，并在完成分配所有列值后将其转换为[PSCustomObject]。
为了区别于其他有用的答案，这个问题使用Select-Xml来简化代码。Select-Xml输出SelectXmlInfo对象，其Node成员包含DOM元素，类似于读取[xml]得到的结果。
Select-Xml -Path Policy.xml -XPath '/Profiles/Profile/Ruleset/Conditions' | ForEach-Object {
# Create initial ordered hashtable
$row = [ordered] @{
policyName = $_.Node.ParentNode.ID
}
foreach( $name in 'Resource', 'Action', 'actor', 'child', 'number' ) {
# Add a property to the hashtable.
# The value is a SelectXmlInfo object, which Export-Csv conveniently converts to string.
$row.$name = $_.Node | Select-Xml -XPath "condition[@name='$name']/text()"
}
# Convert to PSCustomObject and output
[PSCustomObject] $row
} | Export-Csv policy.csv -NoTypeInformation
为您的示例XML输出以下CSV:
"policyName","Resource","Action","actor","child","number"
"policyid1","resource1","action1","actor1","",""
"policyidX","resource4","action4","","child1,child2",""
"policyidY","resource99","action00","","child5","number1"