Powershell convert xml to csv-CodePudding

i have to convert my xml file to a csv file. I really do not have a clue how to do it. I've read many posts but I just don't get it ...

This is my xml:

<?xml version="1.0" encoding="UTF-8"?>

<license_usage xmlns="http://www.example.com/report">
    <start_time timestamp="1643670000">2022-01-31T23:00:00 00:00</start_time>
    <end_time timestamp="1645615967">2022-02-23T11:32:47 00:00</end_time>
    <group_by>hour</group_by>
    <license_time_intervals>
            <license_time_interval timestamp="1643670000" datetime="2022-01-31T23:00:00 00:00">
                                                                <license_count license_type="support_full" reason="login">0</license_count>
                                    <license_count license_type="support_full" reason="extended_contact">0</license_count>
                
                <license_count license_type="support_full">0</license_count>
                                                        
        </license_time_interval>
            <license_time_interval timestamp="1643673600" datetime="2022-02-01T00:00:00 00:00">
                                                                <license_count license_type="support_full" reason="login">2</license_count>
                                    <license_count license_type="support_full" reason="extended_contact">0</license_count>
                
                <license_count license_type="support_full">2</license_count>
                                                        
        </license_time_interval>
            <license_time_interval timestamp="1643677200" datetime="2022-02-01T01:00:00 00:00">
                                                                <license_count license_type="support_full" reason="login">0</license_count>
                                    <license_count license_type="support_full" reason="extended_contact">0</license_count>
                
                <license_count license_type="support_full">0</license_count>
                                                        
        </license_time_interval>
            <license_time_interval timestamp="1643680800" datetime="2022-02-01T02:00:00 00:00">
                                                                <license_count license_type="support_full" reason="login">0</license_count>
                                    <license_count license_type="support_full" reason="extended_contact">0</license_count>
                
                <license_count license_type="support_full">0</license_count>
                                                        
        </license_time_interval>
            <license_time_interval timestamp="1643684400" datetime="2022-02-01T03:00:00 00:00">
                                                                <license_count license_type="support_full" reason="login">0</license_count>
                                    <license_count license_type="support_full" reason="extended_contact">0</license_count>
                
                <license_count license_type="support_full">0</license_count>

        </license_time_interval>
    
    </license_time_intervals>
</license_usage>

And I want to convert it with Powershell to a csv that looks like this:

license_count/0/_license_type,license_count/0/_reason,license_count/0/__text,license_count/1/_license_type,license_count/1/_reason,license_count/1/__text,license_count/2/_license_type,license_count/2/__text,_timestamp,_datetime
support_full,login,0,support_full,extended_contact,0,support_full,0,1643670000,2022-01-31T23:00:00 00:00
support_full,login,2,support_full,extended_contact,0,support_full,2,1643673600,2022-02-01T00:00:00 00:00
support_full,login,0,support_full,extended_contact,0,support_full,0,1643677200,2022-02-01T01:00:00 00:00
support_full,login,0,support_full,extended_contact,0,support_full,0,1643680800,2022-02-01T02:00:00 00:00
support_full,login,0,support_full,extended_contact,0,support_full,0,1643684400,2022-02-01T03:00:00 00:00
support_full,login,0,support_full,extended_contact,0,support_full,0,1643688000,2022-02-01T04:00:00 00:00
support_full,login,3,support_full,extended_contact,0,support_full,3,1643691600,2022-02-01T05:00:00 00:00
and so on

so I call the xmlFile:

$xmlFile = Get-Content "C:\temp\example.xml"

How to go on?

Can you pleeease help me?

Thank you so much!!!

CodePudding user response：

Here is a different interpretation of the data:

# return XmlDocument type from xml content
$xml = [xml](Get-Content "C:\temp\example.xml")

# drill down to the license time intervals
$xml.license_usage.license_time_intervals.license_time_interval | Foreach-Object {
    # current license time interval object
    $interval = $_
    # iterate license_count nodes
    # use calculated properties to mix higher level properties with nested properties
    # license count comes from the node text
    $_.license_count | Select @{n='timestamp';e={$interval.timestamp}},
        @{n='datetime';e={$interval.datetime}},
        license_type,reason,
        @{n='license_count';e={$_.'#text'}}
} | Export-Csv output.csv -NoType

The output CSV will contain headers timestamp,datetime,license_type,reason,license_count. Note that this will result in multiple lines that contain the same timestamp and datetime values because they have multiple associated license_count nodes. Personally, this is easier for me to read and sort in PowerShell and CSV. Scaling wide with the properties could require the code to be more dynamic since you may not always know how many license_count nodes there will be. This code should work regardless of the number of nodes as long as the schema remains the same.