Powershell - Search 2 values into .xml file with regular expression

Question

I hope your well in this particular situation.

Request description: To make my life easier into my day to day, I try to write à powershell script where the goal is to find the each "Name" tables where "reserved" size > 1G

Below an example of output i want to get based on xml file sample below

Output

• Table name: tr_stat_history

• Table size: 3,829623 GB

• Table name: appctrl_exefile

• Table size: 1,58016 GB

xml file sample

<?xml version="1.0" encoding="utf-8" ?>
<data>
<record>
<field value="tr_stat_history" name="Name"/>
<field value="1024936 " name="Rows"/>
<field value="3829623 KB" name="reserved"/>
<field value="38120 KB" name="Data"/>
<field value="120 KB" name="index_size"/>
<field value="56 KB" name="Unused"/>
</record>
<record>
<field value="appctrl_exefile" name="Name"/>
<field value="2149679 " name="Rows"/>
<field value="1580160 KB" name="reserved"/>
<field value="120376 KB" name="Data"/>
<field value="37336 KB" name="index_size"/>
<field value="304 KB" name="Unused"/>
</record>
<record>
<field value="appctrl_pdffile" name="Name"/>
<field value="2149 " name="Rows"/>
<field value="1580 KB" name="reserved"/>
<field value="1203 KB" name="Data"/>
<field value="3733 KB" name="index_size"/>
<field value="508 KB" name="Unused"/>
</record>
</data>

What could be the best way to get my goal?

Thanks by advance,

Answer 1

Both solution are amazing ! Thanks for your help !

I use the following:

# Create XML object to load data into
$xml = New-Object -TypeName System.Xml.XmlDocument

$ResultXMLfile = Read-Host "Path to result.xml"

# Load in XML file
$xml.Load($ResultXMLfile)

# Iterate each child node underneath the data tag
foreach ($node in $xml.data.ChildNodes)
{
    # Get name and reserved fields
    $name = $node.field | Where-Object {$_.name -eq "Name"}
    $reserved = $node.field | Where-Object {$_.name -eq "reserved"}

    # Split on whitespace and take the digit
    # Do KB conversion to bytes by multiplying by 1024
    $kb = [long]$reserved.Value.Split(' ')[0] * 1024

    # Check if bigger than 1GB or 1073741824 bytes
    # Output table name and size if this is true
    if ($kb -gt 1GB)
    {
        Write-Output "Table name: $($name.Value)"
        Write-Output "Table size: $($kb / 1GB) GB"
    }
}

In my case, my xml contains 35k lines, so in few second I got my answer.

Path to result.xml : C:\Users\Usernames\Desktop\Download\all_result\all_result\result.xml
Table name: appctrl_exefile_hst
Table size: 2.74079895019531 GB
Table name: nl_storage_items
Table size: 2.76924133300781 GB

Answer 2

Here's another approach you can try out. I've added comments to explain the logic.

# Create XML object to load data into
$xml = New-Object -TypeName System.Xml.XmlDocument

# Load in XML file
$xml.Load("input.xml")

# Iterate each child node underneath the data tag
foreach ($node in $xml.data.ChildNodes)
{
    # Get name and reserved fields
    $name = $node.field | Where-Object {$_.name -eq "Name"}
    $reserved = $node.field | Where-Object {$_.name -eq "reserved"}

    # Split on whitespace and take the digit
    # Do KB conversion to bytes by multiplying by 1024
    $kb = [long]$reserved.Value.Split(' ')[0] * 1024

    # Check if bigger than 1GB or 1073741824 bytes
    # Output table name and size if this is true
    if ($kb -gt 1GB)
    {
        Write-Output "Table name: $($name.Value)"
        Write-Output "Table size: $($kb / 1GB) GB"
    }
}

Output:

Table name: tr_stat_history
Table size: 3.65221309661865 GB
Table name: appctrl_exefile
Table size: 1.5069580078125 GB

Answer 3

Instead of using regex, you might want to approach this using Powershell's xml parsing capabilities.

I'm not up-to-speed with how to best use xml so following has undoubtly room for improvement but you can get the gist of it using following example.

(
    [xml]@'
    <root>
        <record>
            <field value="tr_stat_history" name="Name"/>
            <field value="1024936 " name="Rows"/>
            <field value="3829623 KB" name="reserved"/>
            <field value="38120 KB" name="Data"/>
            <field value="120 KB" name="index_size"/>
            <field value="56 KB" name="Unused"/>
        </record>
        <record>
            <field value="appctrl_exefile" name="Name"/>
            <field value="2149679 " name="Rows"/>
            <field value="1580160 KB" name="reserved"/>
            <field value="120376 KB" name="Data"/>
            <field value="37336 KB" name="index_size"/>
            <field value="304 KB" name="Unused"/>
        </record>
        <record>
            <field value="appctrl_pdffile" name="Name"/>
            <field value="2149 " name="Rows"/>
            <field value="1580 KB" name="reserved"/>
            <field value="1203 KB" name="Data"/>
            <field value="3733 KB" name="index_size"/>
            <field value="508 KB" name="Unused"/>
        </record>
    </root>
'@
).root.record | % {if (([int]($_.Field[2].Value -split ' ')[0])*1024 -gt 1GB) {$_.Field[0].Value}}

where

• I've added a root element to have valid xml
• Cast the string a an [xml] type
• Loop over all record elements with %
• Test the reserved field. As the value contains a string, I've split it on a space, taken the first element of the resulting array, cast it as an integer and multiply with 1024. After that, it's a simple compare with 1GB
• Only if the reserved field is > 1GB, the name field gets output