locked
List XML child nodes with names of parent nodes in CSV file RRS feed

  • Question

  • I am trying to take an XML file with multiple children of the same parent and list them into a CSV file with the parent name. I have been unable to find information on data structures similar to this even though it must be out there. The data in XML looks like this:

    <CompetencyObject xsi:type="Competency" id="583">
        <name>Competency A </name>
        <children>
          <CompetencyObject xsi:type="Competency" id="819">
            <name>Competency A1</name>
            <children>
              <CompetencyObject xsi:type="Objective" id="939">
                <name>Objective 1</name>
                <children />
              </CompetencyObject>
              <CompetencyObject xsi:type="Objective" id="940">
                <name>Objective 2</name>
                <children />
              </CompetencyObject>
      </children>
       </CompetencyObject>
     </Children>
    <CompetencyObject>

    There is more in the original data but these are the pieces I need to find. I need to take that and list it like this in CSV.

    Competency, Sub-Competency, Objective
    Competency A, Competency A1, Objective 1
    Competency A, Competency A1, Objective 2
    Competency B, Competency B1, Objective 1

    I have never worked with XML files in PowerShell before and have tried this but get either a list of all of the competencies without objectives because they are two layers deeper or the objectives but not the parents if I add children.CompetencyObject before the .childnodes. This does not look exactly like I am hoping for but it would be usable if it gave at least the sub-competency. There are only a few top level competencies so they could be added easily enough in the CSV.

    [xml]$inputFile = Get-Content "competency_d2l_1.xml"
    $inputFile.competency_objects.CompetencyObject.children.CompetencyObject.ChildNodes | Export-Csv "competency_d2l_1.csv" -NoTypeInformation -Delimiter:"," -Encoding:UTF8
    Thanks for any help that you can provide.

    Wednesday, December 4, 2013 6:12 PM

Answers

  • Is the XML file always 3 levels deep like that (Competency, Sub-Competency, Objective), or can there be varying depths of competency records in the file?  It's always a bit of a headache to convert a hierarchical structure into a flat file like this.

    Assuming it's always 3 levels deep, you could do something like this:

    [xml]$inputFile = Get-Content "competency_d2l_1.xml"
    
    $csvRecords = foreach ($competency in $inputFile.competency_objects.CompetencyObject)
    {
        foreach ($subCompetency in $competency.children.CompetencyObject)
        {
            foreach ($objective in $subCompetency.children.CompetencyObject)
            {
                New-Object psobject -Property ([ordered]@{
                    Competency = $competency.Name
                    'Sub-Competency' = $subCompetency.Name
                    Objective = $objective.Name
                })
            }
        }
    }
    
    $csvRecords | Export-Csv .\competency_d2l_1.csv -NoTypeInformation



    • Edited by David Wyatt Wednesday, December 4, 2013 6:44 PM
    • Proposed as answer by Mike Laughlin Wednesday, December 4, 2013 7:21 PM
    • Marked as answer by rnicolson Wednesday, December 4, 2013 7:27 PM
    Wednesday, December 4, 2013 6:41 PM

All replies

  • Is the XML file always 3 levels deep like that (Competency, Sub-Competency, Objective), or can there be varying depths of competency records in the file?  It's always a bit of a headache to convert a hierarchical structure into a flat file like this.

    Assuming it's always 3 levels deep, you could do something like this:

    [xml]$inputFile = Get-Content "competency_d2l_1.xml"
    
    $csvRecords = foreach ($competency in $inputFile.competency_objects.CompetencyObject)
    {
        foreach ($subCompetency in $competency.children.CompetencyObject)
        {
            foreach ($objective in $subCompetency.children.CompetencyObject)
            {
                New-Object psobject -Property ([ordered]@{
                    Competency = $competency.Name
                    'Sub-Competency' = $subCompetency.Name
                    Objective = $objective.Name
                })
            }
        }
    }
    
    $csvRecords | Export-Csv .\competency_d2l_1.csv -NoTypeInformation



    • Edited by David Wyatt Wednesday, December 4, 2013 6:44 PM
    • Proposed as answer by Mike Laughlin Wednesday, December 4, 2013 7:21 PM
    • Marked as answer by rnicolson Wednesday, December 4, 2013 7:27 PM
    Wednesday, December 4, 2013 6:41 PM
  • Thanks David. This worked perfectly.
    Wednesday, December 4, 2013 7:03 PM