XML to CSV convert in powershell (Spacewalk)

user_3319643 0
Viewed 90

I have very little experience with PowerShell and need to convert the below excerpt from XML file to CSV form. Can somebody help me converting this code?

This is the extend of what I was able to do before I stuck. 

$xdoc = new-object System.Xml.XmlDocument
$xdoc.Load("$getUnscheduledErrataPath")

$members = $xdoc.SelectSingleNode("//struct").member.name

$xmldata = ((Select-Xml -Path "$getUnscheduledErrataPath" -XPath "//struct") | ForEach-Object { $_.Node })

XML input:

<?xml version="1.0" encoding="UTF-8"?>
    <methodResponse>
        <params>
            <param>
                <value>
                    <array>
                        <data>
                            <value>
                                <struct>
                                    <member><name>id</name><value><i4>26660</i4></value></member>
                                    <member><name>date</name><value><string>9/4/18</string></value></member>
                                    <member><name>advisory_synopsis</name><value><string> ovmd xenstoreprovider  bug fix update</string></value></member>
                                    <member><name>advisory_name</name><value><string>ELBA-2018-4206</string></value></member>
                                    <member><name>advisory_type</name><value><string>Bug Fix Advisory</string></value></member>
                                </struct>
                            </value>
                            <value>
                                <struct>
                                    <member><name>id</name><value><i4>18094</i4></value></member>
                                    <member><name>date</name><value><string>1/29/19</string></value></member>
                                    <member><name>advisory_synopsis</name><value><string> module-init-tools  bug fix update</string></value></member>
                                    <member><name>advisory_name</name><value><string>ELBA-2019-4527</string></value></member>
                                    <member><name>advisory_type</name><value><string>Bug Fix Advisory</string></value></member>
                                </struct>
                            </value>
                        </data>
                    </array>
                </value>
            </param>
        </params>
    </methodResponse>
csv powershell xml
1 Answers

To create something we can meaningfully convert to CSV, I'd take the following approach (pseudocode):

foreach <struct>
{
    foreach <member>
    {
        collect <name>, parse <value>
    }

    Convert <member> results to object
}

In PowerShell, that would look something like this:

$objects = foreach($struct in $xdoc.SelectNodes('//struct')){
    # Create a dictionary to store the <member> values
    $properties = [ordered]@{}

    # Iterate over <member> child nodes
    foreach($member in $struct.SelectNodes('member')){
        # <name> is easy
        $name = $member.SelectSingleNode('name').innerText

        # for <value> nodes I'm grabbing all ChildNodes and just select the first 1
        $valueNode = $member.SelectSingleNode('value').ChildNodes |Select -First 1

        # Here you could make decisions based on `$valueNode.Name`,
        # like converting <i4> values to an [int] for example
        #
        # But since the purpose is to export to csv, that doesn't make much sense here

        # Add extracted values to dictionary
        $properties[$name] = $valueNode.innerText
    }

    # create object based on extracted properties
    [pscustomobject]$properties
}

# Export resulting objects to CSV
$objects |Export-Csv -Path C:\path\to\output.csv -NoTypeInformation
user_7126490
Related