Read XML Structure in PowerShell and export it to XLS

Question

I am struggling with reading the content of the following XML File with Powershell. I would like to get associative arrays with the CN/OU Part of each <value> as the identifier, and each <roll> and <group> as attributes.

<?xml version="1.0" encoding="UTF-8"?>
<ldap>
    <host>x.x.x.x
        <users>
            <user>DC=Example,DC=internal</user>
        </users>
        <rights>
            <var>distinguishedName
                <value>CN=...,OU=user,OU=...
                    <roll>2</roll>
                </value>
                <value>CN=...,OU=user,OU=...
                    <roll>5</roll>
                    <roll>18</roll>
                </value>
                <value>CN=John Doe*
                    <roll>9</roll>
                </value>
                <value>CN=Domain Admin*
                    <group>Administrator</group>
                    <roll>1</roll>
                </value>
                <value>CN=...,OU=user,...
                    <group>Example Group</group>
                    <roll>8</roll>
                    <roll>12</roll>
                    <roll>14</roll>
                    <roll>15</roll>
                </value>
            </var>
            <var>search:member=&lt;userdn&gt;
                <value>CN=group1*
                    <group>01 Group XY</group>
                    <roll>1</roll>
                </value>
                <value>CN=Client1,OU=*
                    <roll>3</roll>
                </value>
                <value>CN=...,OU=*
                    <roll>5</roll>
                </value>
                <value>CN=ImportantClient06*
                    <group>06ImportantGroup</group>
                    <roll>8</roll>
                    <roll>11</roll>
                    <roll>12</roll>
                </value>
            </var>
        </rights>
    </host>
</ldap>

This was my first Approach:

$d3Xml = [XML] (Get-Content -Path $d3ConfigPath)
$XmlValues = $d3Xml.ldap.host.rights.var

Write-Host "Xml Value: " $XmlValues

I couldn't find any help on this kind of xml structure. I appreciate any help, thank you!

Answer 1

Here is something for you to start with:

$xmlSource = @"
<?xml version="1.0" encoding="UTF-8"?>
<ldap>
<host>x.x.x.x
    <users>
        <user>DC=Example,DC=internal</user>
    </users>
    <rights>
        <var>distinguishedName
            <value>CN=...,OU=user,OU=...
                <roll>2</roll>
            </value>
            <value>CN=...,OU=user,OU=...
                <roll>5</roll>
                <roll>18</roll>
            </value>
            <value>CN=John Doe*
                <roll>9</roll>
            </value>
            <value>CN=Domain Admin*
                <group>Administrator</group>
                <roll>1</roll>
            </value>
            <value>CN=...,OU=user,...
                <group>Example Group</group>
                <roll>8</roll>
                <roll>12</roll>
                <roll>14</roll>
                <roll>15</roll>
            </value>
        </var>
        <var>search:member=&lt;userdn&gt;
            <value>CN=group1*
                <group>01 Group XY</group>
                <roll>1</roll>
            </value>
            <value>CN=Client1,OU=*
                <roll>3</roll>
            </value>
            <value>CN=...,OU=*
                <roll>5</roll>
            </value>
            <value>CN=ImportantClient06*
                <group>06ImportantGroup</group>
                <roll>8</roll>
                <roll>11</roll>
                <roll>12</roll>
            </value>
        </var>
    </rights>
</host>
</ldap>
"@

$xml = [xml] $xmlSource

$xml | select-xml -xpath "//value" | %{ 
        New-Object -TypeName PSObject -Property @{
                CNOU = $_.Node."#text" 
                Group = $_.Node.group
                Roll = $_.Node.roll
        }
    } | select CNOU, group, Roll