Read CSV containing a column of XML data and transform XML data into new CSV.

Transform XML from CSV.ps1

################################################################################################################
#
# Example of XML being parsed.  Each column in the CSV has one of these in its `Submission` field.
#
# <root>
#    <FormField FieldID="FULL_NAME" Caption="Name" Value="Some Person"/>
#    <FormField FieldID="ID" Caption="University ID" Value="0001234567"/>
#    <FormField FieldID="EMAIL" Caption="Preferred Email" Value="SomePerson@gmail.com"/>
#    <FormField FieldID="PHONE_CELL" Caption="Cell Phone" Value="5555659632"/>
# </root>
#
#######################################################################################################################

Import-Csv "Input File.csv"  |`
    ForEach-Object {
        # Transform the XML column into an object.
        $submission = [xml]$_."Submission XML"
        
        # If desired, other columns in the CSV can be accessed also.
        # $submissionDate = $_.submitted

        # Create new object that will be the CSV row.
        $row = [PSCustomObject]@{
            ID = ($submission.root.FormField | ? { $_.FieldID -eq "ID" }).Value
            NAME = ($submission.root.FormField | ? { $_.FieldID -eq "FULL_NAME" }).Value
            EMAIL = ($submission.root.FormField | ? { $_.FieldID -eq "EMAIL" }).Value
            PHONE = ($submission.root.FormField | ? { $_.FieldID -eq "PHONE_CELL" }).Value       
            SOURCE = "Data Source"
        }   

        # Append object to row
        $row | Export-Csv –Append -NoTypeInformation –Path "Output File.csv"
    }