Need help converting XML to CSV

Question

I need to convert the below XML to CSV

<App>
    <SecurityGroup name="Admin">
        <Member username="John1" displayName="JohnDoe"/>
        <Member username="Jane1" displayName="JoeDoe"/>
    </SecurityGroup>
</App>

I tried the below one as well

$xml.app.childnodes | Select-Object @(
    @{l="name";e={ $_.member.username}},
    @{l="display";e={ $_.member.displayname }},
    @{l="group";e={ $_.name }} 
) | Export-CSV "c:/test.csv"

The format looks good but the username field and display name are all coming in the same field instead of individual rows

Security Group    Username           Displayname
Admin             John1 Jane1        JohnDoe JaneDoe

I need the below format

SecurityGroup	Username	Displayname
Admin	John1	JohnDoe
Admin	Jane1	JoeDoe

zett42 · Accepted Answer · 2022-11-16T20:34:25.830

In the calculated properties specified as Select-Object arguments, $_.member creates an array of of all Member elements (due to member access enumeration), which is why your CSV contains multiple property values per field.

What you want to do instead, is descend down to the Member elements already in the first member access chain:

$xml.app.childnodes.member | Select-Object @(
    @{ l='name'; e='username'}
    @{ l='display'; e='displayname'}
    @{ l='group'; e={ $_.ParentNode.name }} 
) | Export-CSV "c:/test.csv"

Output:

"name","display","group" 
"John1","JohnDoe","Admin"
"Jane1","JoeDoe","Admin"

Notes:

When you have direct mapping of input and output properties, you can simplify the code by specifying input property name as a string instead of a scriptblock for the expression (e) of the calculated property, as I did above.
You can omit the comma in the array sub-expression @(…), as each array element is located on its own line. You only need a comma, when specifying multiple array elements per line.
The $xml.app.childnodes.member creates an array of all Member elements contained in any child element under the App element. You might want to restrict the enumeration to SecurityGroup child elements instead, by writing it out like this:
```
$xml.App.SecurityGroup.Member 
```

Need help converting XML to CSV

1 Answers1