In PowerShell, how can I get the XML inner child to populate its parents and format it in a table?

user_16873491 0
Viewed 72

So, I have the following XML file example:

    <root>
    <row>
        <row>
            <PName>Charles</PName>
            <Company>Contoso</Company>
            <ORDERDATE>2022-01-25</ORDERDATE>
            <STREET1>FakeStreet 123</STREET1>
            <City>San Francisco</City>
            <row>
                <ORDERID>0010286731129</ORDERID>
                <row>
                    <ProjectName>Sacramento Expansion</ProjectName>
                    <Name>ProductA</Name>
                    <MBShipped>Y</MBShipped>
                    <MBSKU>100364092</MBSKU>
                    <SerialSKU/>
                    <Color>Lemon</Color>                    
                </row>
            </row>
            <Other>OtherData</Other>
        </row>
        <row>
            <PName>Harvey</PName>
            <Company>Specter</Company>
            <ORDERDATE>2022-01-25</ORDERDATE>
            <STREET1>NotAFake 123</STREET1>
            <City>San Diego</City>
            <row>
                <ORDERID>0010286730526</ORDERID>
                <row>
                    <ProjectName>North Dakota Expansion</ProjectName>
                    <Name>ProductA</Name>
                    <MBShipped>Y</MBShipped>
                    <MBSKU>100364092</MBSKU>
                    <SerialSKU/>
                    <Color>Red</Color>                  
                </row>
                <row>
                    <ProjectName>North Dakota Expansion</ProjectName>
                    <Name>ProductB</Name>
                    <MBShipped>Y</MBShipped>
                    <MBSKU>100364092</MBSKU>
                    <SerialSKU/>
                    <Color>Blue</Color>                 
                </row>
            </row>
            <Other>OtherData</Other>
        </row>
    </row>
    </root>

And I would like that Powershell outputs this:

PName Company ORDERDATE STREET1 City ProjectName Name Color OtherData
Charles Contoso 2022-01-25 FakeStreet 123 San Francisco Sacramento Expansion ProductA Lemon OtherData
Harvey Specter 2022-01-25 NotAFake 123 San Diego North Dakota Expansion ProductA Red OtherData
Harvey Specter 2022-01-25 NotAFake 123 San Diego North Dakota Expansion ProductB Blue OtherData

I've managed to do it with a horrible approach:

[xml]$data = Get-Content ./path/to/xml.xml
$RowsIwant = $data.root.row.row <#note the pipes for select object.#>
| Select-Object -ExpandProperty row -Property PName,Company,Orderdate,Street1,City,Other 
| Select-Object -ExpandProperty row -Property PName,Company,Orderdate,Street1,City,Other
| Select-Object -Property PName,Company,OrderDate,Street1,City,ProjectName,Name,Color,OtherData

$RowsIwant | format-table

Is there a way to do this in a more elegant way? I feel like it's being super inefficient. I tried with a "ForEach-Object" loop but I couldn't get it to access the "innermost" items.

Anyways, I do have a solution but I'd like to improve and get a better understanding on how to deal with the inner items, if you want to throw me a "read about this algorithm!" would be also welcome.

Thanks!

list powershell xml
2 Answers

Assuming you have the XML ($xml in my example) already in memory, you could parse it this way, it would require two loops and you could use [pscustomobject] or a calculated property with Select-Object as in my example.

$result = foreach($line in $xml.root.row.row)
{
    foreach($object in $line.row.row)
    {
        $line | Select-Object @(
            'PName', 'Company', 'ORDERDATE', 'STREET1', 'City'
            @{
                Name = 'ProjectName'
                Expression = { $object.ProjectName }
            }
            @{
                Name = 'Name'
                Expression = { $object.Name }
            }
            @{
                Name = 'Color'
                Expression = { $object.Color }
            }
            @{
                Name = 'OtherData'
                Expression = 'Other'
            }
        )
    }
}

Now $result | Format-Table looks like what you were looking for:

PName   Company ORDERDATE  STREET1        City          ProjectName            Name     Color OtherData
-----   ------- ---------  -------        ----          -----------            ----     ----- ---------
Charles Contoso 2022-01-25 FakeStreet 123 San Francisco Sacramento Expansion   ProductA Lemon OtherData
Harvey  Specter 2022-01-25 NotAFake 123   San Diego     North Dakota Expansion ProductA Red   OtherData
Harvey  Specter 2022-01-25 NotAFake 123   San Diego     North Dakota Expansion ProductB Blue  OtherData
user_153395440

For completion, here's Santiago's approach, but with an PSCustomObject that allows me to rename the columns to whatever I need (I do need it to export to a CSV file), and I think it's also more readable:

[xml]$xml = get-content ./file.xml
$result = foreach($line in $xml.root.row.row)
{
    foreach($object in $line.row.row)
    {
        [PSCustomObject]@{
            'Name'          = $line.PName
            'Company'       = $line.Company 
            'Date'          = $line.OrderDate
            'Street'        = $line.STREET1
            'City'          = $line.City
            'Product Name'  = $object.Name
            'Product Color' = $object.Color
            'Other'         = $line.OtherData
        }
    }
}

Although I liked the select object expanded properties, I didn't understand what it was reading the documentation, I had it right there!

user_168734910
Related