0

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

1 Answer 1

1

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"
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.