Get data from XML elements as a table using Powershell

Question

I try to get data from XML emement as a table. XML file below. XML file is exported Firewall config.

<?xml version="1.0" encoding="UTF-8"?>
<fpc4:Root xmlns:fpc4="http://schemas.microsoft.com/isa/config-4" xmlns:dt="urn:schemas-microsoft-com:datatypes" StorageName="FPC" StorageType="0">
    <fpc4:Build dt:dt="string">7.0.9193.500</fpc4:Build>
    <fpc4:Comment dt:dt="string">0</fpc4:Comment>
    <fpc4:Edition dt:dt="int">32</fpc4:Edition>
    <fpc4:EnterpriseLevel dt:dt="int">2</fpc4:EnterpriseLevel>
    <fpc4:ExportItemClassCLSID dt:dt="string">{59740B3A-8771-492C-AF59-7764F4F939EF}</fpc4:ExportItemClassCLSID>
    <fpc4:ExportItemCompatibilityVersion dt:dt="int">4</fpc4:ExportItemCompatibilityVersion>
    <fpc4:ExportItemScope dt:dt="int">0</fpc4:ExportItemScope>
    <fpc4:ExportItemStorageName dt:dt="string">{A86DF49D-0078-48D5-8A58-A970482FA6D6}</fpc4:ExportItemStorageName>
    <fpc4:IsaXmlVersion dt:dt="string">9.0</fpc4:IsaXmlVersion>
    <fpc4:OptionalData dt:dt="int">0</fpc4:OptionalData>
    <fpc4:Upgrade dt:dt="boolean">0</fpc4:Upgrade>
    <fpc4:ConfigurationMode dt:dt="int">0</fpc4:ConfigurationMode>
    <fpc4:Arrays StorageName="Arrays" StorageType="0">
        <fpc4:Array StorageName="{7149EEA4-E267-43EA-A7C8-25C3FF1EC368}" StorageType="0">
            <fpc4:AdminMajorVersion dt:dt="int">0</fpc4:AdminMajorVersion>
            <fpc4:AdminMinorVersion dt:dt="int">0</fpc4:AdminMinorVersion>
            <fpc4:Components dt:dt="int">-1</fpc4:Components>
            <fpc4:DNSName dt:dt="string"/>
            <fpc4:Name dt:dt="string"/>
            <fpc4:Version dt:dt="string">0</fpc4:Version>
                <fpc4:Name dt:dt="string"/>
            <fpc4:RuleElements StorageName="RuleElements" StorageType="0">
                <fpc4:ComputerSets StorageName="ComputerSets" StorageType="0">
                    <fpc4:ComputerSet StorageName="{F51D0A66-40CA-4E1E-9454-A1F73834CEFC}" StorageType="2">
                        <fpc4:Name dt:dt="string">ScanSafe</fpc4:Name>
                        <fpc4:Computers StorageName="Computers" StorageType="2">
                            <fpc4:Computer StorageName="{9A946B36-12C6-484F-9729-97D51C53017D}" StorageType="2">
                                <fpc4:IPAddress dt:dt="string">100.93.231.200</fpc4:IPAddress>
                                <fpc4:Name dt:dt="string">1363.scans.net</fpc4:Name>
                            </fpc4:Computer>
                        </fpc4:Computers>
                    </fpc4:ComputerSet>
                    <fpc4:ComputerSet StorageName="{0598ED7C-CA3E-4461-A230-B2669B35872F}" StorageType="2">
                        <fpc4:Name dt:dt="string">Subscribers</fpc4:Name>
                        <fpc4:Computers StorageName="Computers" StorageType="2">
                            <fpc4:Computer StorageName="{1FF8B477-7DF2-4117-978A-044377B5958A}" StorageType="2">
                                <fpc4:IPAddress dt:dt="string">111.111.111.111</fpc4:IPAddress>
                                <fpc4:Name dt:dt="string">Client1</fpc4:Name>
                            </fpc4:Computer>
                            <fpc4:Computer StorageName="{43F2C2EB-BB08-4B2E-A05C-9D61E299820D}" StorageType="2">
                                <fpc4:IPAddress dt:dt="string">222.222.222.333</fpc4:IPAddress>
                                <fpc4:Name dt:dt="string">Client2</fpc4:Name>
                            </fpc4:Computer>
                            <fpc4:Computer StorageName="{6F1CA232-9C17-48B8-A794-65037301C24C}" StorageType="2">
                                <fpc4:IPAddress dt:dt="string">111.111.111.112</fpc4:IPAddress>
                                <fpc4:Name dt:dt="string">Client3</fpc4:Name>
                            </fpc4:Computer>
                        </fpc4:Computers>
                        <fpc4:AddressRanges StorageName="AddressRanges" StorageType="2">
                            <fpc4:AddressRange StorageName="{C7CD37CF-788B-45A0-9A44-2C0DA7C00D37}" StorageType="2">
                                <fpc4:IPFrom dt:dt="string">222.222.222.338</fpc4:IPFrom>
                                <fpc4:IPTo dt:dt="string">222.222.222.340</fpc4:IPTo>
                                <fpc4:Name dt:dt="string">Range 1 </fpc4:Name>
                            </fpc4:AddressRange>
                            <fpc4:AddressRange StorageName="{BC77F5FD-1CBF-4EDB-9968-2DD5ADA9090B}" StorageType="2">
                                <fpc4:IPFrom dt:dt="string">222.222.222.390</fpc4:IPFrom>
                                <fpc4:IPTo dt:dt="string">222.222.222.400</fpc4:IPTo>
                                <fpc4:Name dt:dt="string">Range 2</fpc4:Name>
                            </fpc4:AddressRange>
                        </fpc4:AddressRanges>
                    </fpc4:ComputerSet>
                </fpc4:ComputerSets>
            </fpc4:RuleElements>
        </fpc4:Array>
    </fpc4:Arrays>
</fpc4:Root>

I am interested in data inside of following element.

<fpc4:ComputerSet StorageName="{0598ED7C-CA3E-4461-A230-B2669B35872F}" StorageType="2">

So far what i have tried. Following

PS H:\> $XMLfile = 'C:\tmp\asd.xml'
[XML]$xml = Get-Content $XMLfile

$xml.Root.Arrays.Array.RuleElements.ComputerSets.ComputerSet.Item(1)

Which returns following, how can i make it to show data inside the Name/Computer elements ?

StorageName   : {0598ED7C-CA3E-4461-A230-B2669B35872F}
StorageType   : 2
Name          : Name
Computers     : Computers
AddressRanges : AddressRanges

$xml.Root.Arrays.Array.RuleElements.ComputerSets.InnerText | Format-Table

Returns all data as a singe string which is unreadable. | Format-Table does not work.

I would like to get the data in a table like this

Name            IPAddress             
Client1         111.111.111.111
Client2         222.222.222.333
Name            AddressRanges
Range 1         222.222.222.338 
                222.222.222.340
Range 2         222.222.222.390
                222.222.222.400

I assume foreach loop/cycle would help, but currently i have no idea how to do this.

Thank You.

Answer 1

You need two loops in which you can construct custom objects with just the properties of interest, which both facilitates later programmatic processing and outputting the tabular format of interest:

# Read the XML file into a System.Xml.XmlDocument DOM ([xml]).
$xml = [xml] (Get-Content -Raw 'C:\tmp\asd.xml')

# Get a reference to the common parent element of the elements of interest.
$parentEl = $xml.Root.Arrays.Array.RuleElements.ComputerSets.ComputerSet[1]

# Loop over computers and create custom objects.
$computers = foreach ($computer in $parentEl.Computers.Computer) { 
  [pscustomobject] @{ 
    Name = $computer.Name.InnerText
    IPAddress = $computer.IPAddress.InnerText 
  } 
}

# Loop over address ranges.
$ranges = foreach ($range in $parentEl.AddressRanges.AddressRange) { 
  [pscustomobject] @{ 
     Name = $range.Name.InnerText
     AddressRange = $range.IPFrom.InnerText, $range.IPTo.InnerText
  } 
}

# For display, print the computers and range separately.
$computers | Format-Table
$ranges | Format-Table

Note the need to use Format-Table individually; if you sent both collections at once to Format-Table ($computer, $ranges | Format-Table), the properties of the objects in the first collection alone would determine the table columns, so you wouldn't see the properties unique to the objects in the second collection; that is, outputting $computer, $ranges would show the computers as expected, but would be missing the AddressRange property values for the ranges.

Remember that Format-* cmdlets must only ever be used to create for-display representations, not for further programmatic processing; in the case at hand, you can use $computers and $ranges for the latter.

Note that the same applies to Out-File / >: they save the for-display representations to a file, using the Format-* cmdlets implicitly. In the case at hand, because the input objects have fewer than 4 properties, Format-Table is implicitly applied.

Therefore, if you want to capture the two for-display table representations in a single output file, you must again use individual commands:

$computers | Out-File -FilePath $out_file          # or: $computers >  $out_file
$ranges    | Out-File -FilePath $out_file -Append  # or: $ranges    >> $out_file

The above yields:

Name    IPAddress
----    ---------
Client1 111.111.111.111
Client2 222.222.222.333
Client3 111.111.111.112


Name     AddressRange
----     ------------
Range 1  {222.222.222.338, 222.222.222.340}
Range 2  {222.222.222.390, 222.222.222.400}

Answer 2

I'd suggest using xpath and then using the member access operator (.) from there:

[xml]$xml = Get-Content -Path $xmlFile
$computerSets = $xml.SelectNodes('//ComputerSets')

foreach ($set in $computerSets) {
    $pcs = $set.Computers # => array of Computers
    $ips = $set.AddressRanges # => array of AddressRange
}

and so on in that loop for each set.