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.

mklement0 · Accepted Answer · 2019-07-31T13:59:37.357

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}

score 0 · Answer 2 · answered Jul 31 '19 at 12:09

0

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.

answered Jul 31 '19 at 12:09

Maximilian Burszley

18,243
4
34
63

The OP already had the parent element of interest; their difficulty was in extracting data selectively from the children and producing tabular output from it. Note that you cannot extract namespace-qualified elements by mere tag name, the way you're suggesting in your XPath query. – mklement0 Jul 31 '19 at 14:10
@mklement0 Thanks for the feedback. I am glad XML is dying; wish it was sooner. Namespaces are a headache. – Maximilian Burszley Jul 31 '19 at 14:14
1

Agreed; case in point, with `.SelectNodes()`: https://stackoverflow.com/a/51379117/45375; `Select-Xml` makes it slightly less painful: https://stackoverflow.com/a/49387055/45375 – mklement0 Jul 31 '19 at 14:22

Get data from XML elements as a table using Powershell

2 Answers2