Powershell XML add key value as element-CodePudding

Objective: Save xml as csv

Problem: required data is contained within key

Example code:

[xml] $sample = '<TestTop><TestElements><td field="head.office">ABC</td><td field="head.number">101</td><td field="head.colour">Blue</td><key><office>ABC</office><number>101</number><line>1</line></key></TestElements><TestElements><td field="head.office">ABC</td><td field="head.number">102</td><td field="head.colour">Red</td><key><office>ABC</office><number>102</number><line>2</line></key></TestElements></TestTop>'

Execute: $sample.TestTop.ChildNodes.td

Returns:

field       #text
-----       -----
head.office ABC  
head.number 101  
head.colour Blue 
head.office ABC  
head.number 102  
head.colour Red

Execute: $sample.TestTop.ChildNodes.key

Returns:

office number line
------ ------ ----
ABC    101    1   
ABC    102    2

Desired result: csv output with key.line appended to element results i.e.

field       #text
-----       -----
head.office ABC    
head.number 101  
head.colour Blue
line        1
head.office ABC  
head.number 102  
head.colour Red
line        2

CodePudding user response：

You can't do top-level member-access enumeration with $sample.TestTop.ChildNodes.td because you want to interleave td rows with their corresponding line = ... rows, so you'll need to iterate over the TestElements nodes yourself...

[xml] $sample = @"
<TestTop>
  <TestElements>
    <td field="head.office">ABC</td>
    <td field="head.number">101</td>
    <td field="head.colour">Blue</td>
    <key>
      <office>ABC</office>
      <number>101</number>
      <line>1</line>
    </key>
  </TestElements>
  <TestElements>
    <td field="head.office">ABC</td>
    <td field="head.number">102</td>
    <td field="head.colour">Red</td>
    <key>
      <office>ABC</office>
      <number>102</number>
      <line>2</line>
    </key>
  </TestElements>
</TestTop>
"@

$data = $sample.TestTop.TestElements | foreach-object {
   $_.td | foreach-object { [pscustomobject] [ordered] @{ "field"=$_.field; "#text"=$_.InnerText } }
   [pscustomobject] [ordered] @{ "field"="line"; "#text"=$_.key.line }
}

$data
# field       #text
# -----       -----
# head.office ABC
# head.number 101
# head.colour Blue
# line        1
# head.office ABC
# head.number 102
# head.colour Red
# line        2

$data | export-csv "myfile.csv"