Powershell xml to CSV select node based on Subset's info

March 16, 2022

I’m trying to convert an XML file to CSV. To make that I need to check values of some sets ("laborid" and "kundenid" ) and also subsets
("tourid"and "tourzeit") based on a subset with "befundtour" equal true:

<customer>
    <laborid>XXXXX</laborid>
    <kundenid>AAAAA</kundenid>
    <ziellabor>BBB</ziellabor>
    <tourpos>
        <tourid>073316</tourid>
        <tourzeit>10:50:00</tourzeit>
        <MO>true</MO>
        <DI>true</DI>
        <MI>true</MI>
        <DO>true</DO>
        <FR>true</FR>
        <SA>false</SA>
        <SO>false</SO>
        <gpsLaenge>9151569</gpsLaenge>
        <gpsBreite>48432079</gpsBreite>
        <befundtour>true</befundtour>
        <abholtour>true</abholtour>
        <druck>true</druck>
        <versandtour>true</versandtour>
        <status>A</status>
    </tourpos>
    <tourpos>
        <tourid>773802</tourid>
        <tourzeit>00:50:00</tourzeit>
        <bemerkung><![CDATA[Briefkasten ist offen (Klappe)!]]></bemerkung>
        <MO>false</MO>
        <DI>false</DI>
        <MI>false</MI>
        <DO>false</DO>
        <FR>false</FR>
        <SA>false</SA>
        <SO>false</SO>
        <gpsLaenge>9151569</gpsLaenge>
        <gpsBreite>48432079</gpsBreite>
        <befundtour>false</befundtour>
        <abholtour>true</abholtour>
        <druck>true</druck>
        <versandtour>false</versandtour>
        <status>A</status>
    </tourpos>
</customer>

after all I would like to have something like this:

XXXXX|0|AAAAA|073316|10:50|

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.
Visit Medevel

But in the end I get something like this:

XXXXX|0|AAAAA|073316 773802|10:50:00 00:50:00

The script :

$file= .\FFFFF.*.xml
if (Test-Path -path $file){
$xml = [xml](Get-Content $file)
$xml.tourCustomer.customer | Select-Object  @(
@{l="LaborId";e={$_.laborid}},
@{l="Kundennr";e={0}},
@{l="Kürzel";e={$_.kundenid}},
@{l="Tour";e={$_.tourpos.tourid}},
@{l="Zeit";e={$_.tourpos.tourzeit}}) | Where-Object {$null -ne $_.Tour -and $null -ne $_.Zeit} 
|ConvertTo-Csv -Delimiter '|'-NoTypeInformation  | % {$_ -replace '"', ""} |  Out-File 
.\output_2.csv -Force -Encoding UTF8
}else
{return
}

In fact, I can not have "LaborId" only for the subset "befundtour" with true, but unfortunately I have both.

>Solution :

$xml.tourCustomer.customer | Select-Object  @(
    @{l="LaborId";e={ $_.laborid }},
    @{l="Kundennr";e={ 0 }},
    @{l="Kürzel";e={ $_.kundenid }},
    @{l="Tour";e={ ($_.tourpos | where befundtour -eq 'true').tourid }},
    @{l="Zeit";e={ ($_.tourpos | where befundtour -eq 'true').tourzeit }}
)

outputs

LaborId  : XXXXX
Kundennr : 0
Kürzel   : AAAAA
Tour     : 073316
Zeit     : 10:50:00

or, after passing it through ConvertTo-CSV -NoTypeInformation

"LaborId","Kundennr","Kürzel","Tour","Zeit"
"XXXXX","0","AAAAA","073316","10:50:00"

That will work as long as there is only ever one <befundtour>true</befundtour> per <customer>. If that’s not guaranteed, you need to define more specific filter conditions, or use -join to make an "in-column" list of values.