Parse an XML file with Powershell

Question

I'm trying to parse an xml file and can't seem to extract the pieces I want. The xml file is for a homegrown system and I don't have any control over its layout.

The file looks like this –

$doc = [xml]@'
<?xml version="1.0" encoding="utf-8"?>
<rbacx >
    <namespace namespaceName="Team Name" namespaceShortName="ABC"/>
    <attributeValues>
        <attributeValue id="Role=Administrator">
            <value><![CDATA[Administrator]]></value>
            <attributes>
                <attribute name="Glossary">
                    <attributeValues>
                        <attributeValue><value><![CDATA[Administrator (service accounts)]]></value></attributeValue>
                    </attributeValues>
                </attribute>
            </attributes>
        </attributeValue>
        <attributeValue id="Role=Operator">
            <value><![CDATA[Operator]]></value>
            <attributes>
                <attribute name="Glossary">
                    <attributeValues>
                        <attributeValue><value><![CDATA[Operator (all accounts)]]></value></attributeValue>
                    </attributeValues>
                </attribute>
            </attributes>
        </attributeValue>
    </attributeValues>
    <accounts>
        <account id="FRED">
            <name><![CDATA[FRED@xyz.com]]></name>
            <endPoint>ABC</endPoint>
            <domain>ABC</domain>
            <comments/>
            <attributes>
                <attribute name="AppBoRID">
                    <attributeValues>
                        <attributeValue><value><![CDATA[FRED]]></value></attributeValue>
                    </attributeValues>
                </attribute>
                <attribute name="Role">
                    <attributeValues><attributeValueRef id="Role=Operator"/></attributeValues>
                </attribute>
            </attributes>
        </account>
        <account id="BARNEY">
            <name><![CDATA[BARNEY@xyz.com]]></name>
            <endPoint>ABC</endPoint>
            <domain>ABC</domain>
            <comments/>
            <attributes>
                <attribute name="AppBoRID">
                    <attributeValues>
                        <attributeValue><value><![CDATA[BARNEY]]></value></attributeValue>
                    </attributeValues>
                </attribute>
                <attribute name="Role">
                    <attributeValues><attributeValueRef id="Role=Administrator"/></attributeValues>
                </attribute>
            </attributes>
        </account>
        <account id="NonPeopleID_CC1234">
            <name><![CDATA[WILMA@xyz.com]]></name>
            <endPoint>ABC</endPoint>
            <domain>ABC</domain>
            <comments/>
            <attributes>
                <attribute name="appUserName">
                    <attributeValues>
                        <attributeValue><value><![CDATA[WILMA]]></value></attributeValue>
                    </attributeValues>
                </attribute>
                <attribute name="CostCentre">
                    <attributeValues>
                        <attributeValue><value><![CDATA[1234]]></value></attributeValue>
                    </attributeValues>
                </attribute>
                <attribute name="Bank_Number">
                    <attributeValues>
                        <attributeValue><value><![CDATA[0000]]></value></attributeValue>
                    </attributeValues>
                </attribute>
                <attribute name="Directory">
                    <attributeValues>
                        <attributeValue><value><![CDATA[XYZ]]></value></attributeValue>
                    </attributeValues>
                </attribute>
                <attribute name="Role">
                    <attributeValues><attributeValueRef id="Role=Administrator"/></attributeValues>
                </attribute>
            </attributes>
        </account>
        <account id="NonPeopleID_CC1234">
            <name><![CDATA[BETTY@xyz.com]]></name>
            <endPoint>ABC</endPoint>
            <domain>ABC</domain>
            <comments/>
            <attributes>
                <attribute name="appUserName">
                    <attributeValues>
                        <attributeValue><value><![CDATA[BETTY]]></value></attributeValue>
                    </attributeValues>
                </attribute>
                <attribute name="CostCentre">
                    <attributeValues>
                        <attributeValue><value><![CDATA[1234]]></value></attributeValue>
                    </attributeValues>
                </attribute>
                <attribute name="Bank_Number">
                    <attributeValues>
                        <attributeValue><value><![CDATA[0000]]></value></attributeValue>
                    </attributeValues>
                </attribute>
                <attribute name="Directory">
                    <attributeValues>
                        <attributeValue><value><![CDATA[XYZ]]></value></attributeValue>
                    </attributeValues>
                </attribute>
                <attribute name="Role">
                    <attributeValues><attributeValueRef id="Role=Operator"/></attributeValues>
                </attribute>
            </attributes>
        </account>
    </accounts>
</rbacx>

'@

I'd like to get back output looking like this –

Name            Role
FRED            Operator
BARNEY          Administrator
WILMA           Administrator
BETTY           Operator

I'm able to get the name@domain with this –

$Doc.rbacx.accounts.account.name 

where it returns - 

#cdata-section
--------------
FRED@xyz.com  
BARNEY@xyz.com
WILMA@xyz.com 
BETTY@xyz.com

I can get all the value attributes with this -

$Doc.rbacx.accounts.account.attributes.attribute.attributevalues.attributevalue.value 

where it returns - 

#cdata-section
--------------
FRED          
BARNEY        
WILMA         
1234          
0000          
XYZ           
BETTY         
1234          
0000          
XYZ

I can't seem to get the role associated with the user returned. I'm thinking it should be along the lines of this –

$Doc.rbacx.accounts.account.attributes.attribute.attributevalues.attributevalue.attributeValueRef

However that doesn't return anything.

Any thoughts on how I can get the User & its associated Role output here?

arjabbar · Answer 1 · 2020-09-15T15:25:45.960

2

Once you are familiar with XPath the rest is not too bad. Also, what makes this harder is that the role names are Role=Operator instead of just Operator. So you have to do a string split on the value of the attribute id on the attributeValueRef nodes. In the end you'll have something like this.

Edit: The coalesce operator only runs in Powershell 7+

Lots of alternatives given your PowerShell version can be found here.

$doc.SelectNodes("//accounts/account") | % { [pscustomobject]@{Name = $_.SelectSingleNode('attributes/attribute[@name="appUserName"]//value').'#cdata-section' ?? $_.id; Role = $_.SelectSingleNode('attributes/attribute[@name="Role"]//attributeValueRef/@id').value.Split('=')[1] } }

The output is a [PSCustomObject]. Running this will show your output in the console, but if you really need this to just be a string, you can pipe this through Out-String or Format-Table.

edited Sep 15 '20 at 15:25

answered Sep 15 '20 at 14:11

arjabbar

6,044
4
30
46

1

Just realized that the actual names of the users are in different nodes which makes this more of a pain. – arjabbar Sep 15 '20 at 14:16
Unfortunately I'm getting an error - "Unexpected token '??' in expression or statement." I assume that pertains to me being on v5.1 as the next response indicates. – surge3333 Sep 15 '20 at 15:20
My bad. This is PowerShell 7. If you have PowerShell 5.1, then you'd have to break out that For-Each block into multiple lines and use an if/else instead. – arjabbar Sep 15 '20 at 15:27

score 1 · Accepted Answer · answered Sep 15 '20 at 14:40

Arjabbar's answer is fantastic, unless you are on 5.1 where the ?? syntax is not supported. I'm sure there's a cleaner way to check but this will achieve your result. If the account has an appusername it will use that, otherwise it will use the ID.

$doc.SelectNodes("//accounts/account") | foreach {
    $name = if($appusername = $_.SelectSingleNode('attributes/attribute[@name="appUserName"]//value').'#cdata-section')
    {
        $appusername
    }
    else
    {
        $_.id
    }
    [PSCustomObject]@{
        Name = $name
        Role = $_.SelectSingleNode('attributes/attribute[@name="Role"]//attributeValueRef/@id').value.Split('=') | select -last 1
    }
}

Name   Role         
----   ----         
FRED   Operator     
BARNEY Administrator
WILMA  Administrator
BETTY  Operator

Thanks. This looks to give me exactly what I'm after. – surge3333 Sep 15 '20 at 15:21 — surge3333, Sep 15 '20 at 15:21

Parse an XML file with Powershell

2 Answers2