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

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!

CodePudding user response：

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

CodePudding user response：

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!