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[[email protected]]]></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[[email protected]]]></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[[email protected]]]></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[[email protected]]]></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
--------------
[email protected]  
[email protected]
[email protected] 
[email protected]

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 · Accepted Answer · 2020-09-15 15:25:45Z

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, 2020 at 15:25

answered Sep 15, 2020 at 14:11

arjabbar

6,4444 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

arjabbar Over a year ago

Just realized that the actual names of the users are in different nodes which makes this more of a pain.

surge3333 Over a year ago

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.

arjabbar Over a year ago

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.

Doug Maurer · Accepted Answer · 2020-09-15 14:40:33Z

1

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

answered Sep 15, 2020 at 14:40

Doug Maurer

8,9773 gold badges16 silver badges18 bronze badges

1 Comment

surge3333 Over a year ago

Thanks. This looks to give me exactly what I'm after.

Collectives™ on Stack Overflow

Parse an XML file with Powershell

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related