Consider XSLT, the special-purpose, industry-wide language designed to transform XML files into other XML, HTML, and even text files like CSVs! PowerShell can run XSLT with built-in System.Xml.Xsl or other, third-party XSLT processors (Saxon, Xalan, etc.) which can run higher versions 2.0 and 3.0.

Specifically, below XSLT 1.0 will walk through every descendant element and attribute of root (assumed to be <AuditRecord>) and extracts underlying text values in comma separation. To create unique headers, ancestor elements three levels up are concatenated together with underscore separators. For elements that do not have such ancestor, underscores are stretched out with no values between. Also, some elements do not have any text and hence return empty cells. Consider cleaning this up later on!

XSLT (save as .xsl file, a special .xml file)

<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text" indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="/AuditRecord">
        <!-- HEADERS -->
        <xsl:for-each select="descendant::*/@* | descendant::*">
            <xsl:value-of select="concat(name(../../..), '_', name(../..), '_', ../@Name, '_', name(..), '_', name())"/>
            <xsl:if test="position() != last()">
                <xsl:text>,</xsl:text>
            </xsl:if>
            <xsl:if test="position() = last()">
                <xsl:text>
</xsl:text>
            </xsl:if>
        </xsl:for-each>
        <!-- ROWS -->
        <xsl:apply-templates select="descendant::*"/>
    </xsl:template>

    <!-- PARSE ELEMENTS -->
    <xsl:template match="*">
        <xsl:apply-templates select="@*"/>
        <xsl:value-of select="text()"/>
        <xsl:if test="position() != last()">
            <xsl:text>,</xsl:text>
        </xsl:if>
        <xsl:if test="position() = last()">
            <xsl:text>
</xsl:text>
        </xsl:if>
    </xsl:template>

    <!-- PARSE ATTRIBUTES -->
    <xsl:template match="@*">
        <xsl:value-of select="."/>
        <xsl:if test="position() != last()">
            <xsl:text>,</xsl:text>
        </xsl:if>
    </xsl:template>
</xsl:stylesheet>

Output (CSV shown in markdown table version)

| ___AuditRecord_PIUser | _AuditRecord_piadmin_PIUser_UserID | _AuditRecord_piadmin_PIUser_Name | ___AuditRecord_PITime     | _AuditRecord__PITime_UTCSeconds | _AuditRecord__PITime_LocalDate | ___AuditRecord_PIConfigurationDB     | _AuditRecord__PIConfigurationDB_PIModules | AuditRecord_PIConfigurationDB__PIModules_Action | AuditRecord_PIConfigurationDB__PIModules_PIModule | PIConfigurationDB_PIModules_Database_PIModule_UniqueID | PIConfigurationDB_PIModules_Database_PIModule_Name | PIConfigurationDB_PIModules_Database_PIModule_PIModuleAttributes | PIModules_PIModule__PIModuleAttributes_PIModuleAttribute | PIModule_PIModuleAttributes_ModifyDate_PIModuleAttribute_Name | PIModule_PIModuleAttributes_ModifyDate_PIModuleAttribute_Value | PIModuleAttributes_PIModuleAttribute__Value_Before | PIModuleAttribute_Value__Before_Type | PIModuleAttributes_PIModuleAttribute__Value_After | PIModuleAttribute_Value__After_Type | PIModules_PIModule__PIModuleAttributes_PIModuleAttribute | PIModule_PIModuleAttributes_Revision_PIModuleAttribute_Name | PIModule_PIModuleAttributes_Revision_PIModuleAttribute_Value | PIModuleAttributes_PIModuleAttribute__Value_Before | PIModuleAttribute_Value__Before_Type | PIModuleAttributes_PIModuleAttribute__Value_After | PIModuleAttribute_Value__After_Type | PIConfigurationDB_PIModules_Database_PIModule_PIProperties | PIModules_PIModule__PIProperties_PIProperty | PIModule_PIProperties_TemplateDataSet_PIProperty_Name | PIModule_PIProperties_TemplateDataSet_PIProperty_Action | PIModule_PIProperties_TemplateDataSet_PIProperty_ParentUNC_Name | PIModule_PIProperties_TemplateDataSet_PIProperty_Value | PIProperties_PIProperty__Value_Before | PIProperty_Value__Before_Type | PIProperties_PIProperty__Value_After | PIProperty_Value__After_Type |
|-----------------------|------------------------------------|----------------------------------|---------------------------|---------------------------------|--------------------------------|--------------------------------------|-------------------------------------------|-------------------------------------------------|---------------------------------------------------|--------------------------------------------------------|----------------------------------------------------|------------------------------------------------------------------|----------------------------------------------------------|---------------------------------------------------------------|----------------------------------------------------------------|----------------------------------------------------|--------------------------------------|---------------------------------------------------|-------------------------------------|----------------------------------------------------------|-------------------------------------------------------------|--------------------------------------------------------------|----------------------------------------------------|--------------------------------------|---------------------------------------------------|-------------------------------------|------------------------------------------------------------|---------------------------------------------|-------------------------------------------------------|---------------------------------------------------------|-----------------------------------------------------------------|--------------------------------------------------------|---------------------------------------|-------------------------------|--------------------------------------|------------------------------|
| 1                     | piadmin                            | 1711662501                       | 2024-03-28T17:48:21-04:00 |                                 | Edit                           | feea9e80-3d3f-4f45-b58d-275e4845bcde | 31-Dec-69 16:00:01                        | Database                                        |                                                   | ModifyDate                                             |                                                    | xs:dateTime2024-03-28T17:47:42-04:00                             | xs:dateTime2024-03-28T17:48:21-04:00                     | Revision                                                      |                                                                | xs:long35738                                       | xs:long35739                         |                                                   | TemplateDataSet                     | Edit                                                     | \PIProperties                                              |                                                              | xs:hexBinary                                       | xs:hexBinary                         |                                                   |                                     |                                                            |                                             |                                                       |                                                         |                                                                 |                                                        |                                       |                               |                                      |                              |

PowerShell

# Define the directory containing the XML files
$directory = "C:test"

# Get all XML files in the directory
$xmlFiles = Get-ChildItem -Path $directory -Filter *.xml

# Load XSLT
$xslt = New-Object System.Xml.Xsl.XslCompiledTransform;
$xslt.Load("C:pathtostyle.xsl");

# Loop through each XML file
foreach ($file in $xmlFiles) {
    # Define the output CSV file path
    $csvFilePath = Join-Path -Path $directory -ChildPath ($file.BaseName + ".csv")

    # Run transformation (XML -> CSV)
    $xslt.Transform($file.FullName, $csvFilePath);
}

OK so you changed your question a bit and gave a new sample file so i rewrote my answer to reflect your changes.

$Directory = 'C:Test'

Get-ChildItem -Path $Directory -Filter *.xml | 
    ForEach-Object{
        $CsvFilePath = Join-Path -Path $Directory -ChildPath ($_.BaseName + ".csv")
        Select-Xml -Path $_.FullName -XPath "//*" | 
        Where-Object{$_.Node.ToString() -eq 'AuditRecord'} | 
        Select-Object -ExpandProperty Node |
        ForEach-Object{
            [PSCustomObject]@{
                AuditRecordID = $_.AuditRecordID
                UserID = $_.PIUser.UserID
                UserName = $_.PIUser.Name
                UTCSeconds = $_.PITime.UTCSeconds
                LocalDate = $_.PITime.LocalDate
                PIModuleAction = $_.PIConfigurationDB.PIModules.Action
                PIModuleUniqueID = $_.PIConfigurationDB.PIModules.PIModule.UniqueID -join ", "
                PIModuleName = $_.PIConfigurationDB.PIModules.PIModule.Name
                PIModuleAttribName = $_.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Name -join ", "
                DateTimeXSBefore = $_.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Value.Before.Type[0]
                DateTimeXSAfter = $_.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Value.After.Type[0]
                PIModuleAttribName2 = $_.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Name[1]
                LongBefore = $_.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Value.Before.Type[1]
                LongAfter = $_.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Value.After.Type[1]
                PIModuleAttribName3 = $_.PIConfigurationDB.PIModules.PIModule.PIProperties.PIProperty.Name
                PIPropertyName = $_.PIConfigurationDB.PIModules.PIModule.PIProperties.PIProperty.Action
                PIPropertyAction = $_.PIConfigurationDB.PIModules.PIModule.PIProperties.PIProperty.ParentUNC_Name
                hexbinBefore = $_.PIConfigurationDB.PIModules.PIModule.PIProperties.PIProperty.Value.Before.Type
                hexbinAfter = $_.PIConfigurationDB.PIModules.PIModule.PIProperties.PIProperty.Value.After.Type       
            }
        } | Export-Csv -Path $CsvFilePath -NoTypeInformation
    }

Will result in

"AuditRecordID","UserID","UserName","UTCSeconds","LocalDate","PIModuleAction","PIModuleUniqueID","PIModuleName","PIModuleAttribName","DateTimeXSBefore","DateTimeXSAfter","PIModuleAttribName2","LongBefore","LongAfter","PIModuleAttribName3","PIPropertyName","PIPropertyAction","hexbinBefore","hexbinAfter"
"b066447a-20de-4659-8e5e-41ecb27d9d39","1","piadmin","1711662501","2024-03-28T17:48:21-04:00","Edit","feea9e80-3d3f-4f45-b58d-275e4845bcde, 31-Dec-69 16:00:01","Database","ModifyDate, Revision","xs:dateTime","xs:dateTime","Revision","xs:long","xs:long","TemplateDataSet","Edit","\PIProperties","xs:hexBinary","xs:hexBinary"
"d6588207-7ab2-4a4f-bfba-f62621a2cae2","1","piadmin","1711662705","2024-03-28T17:51:45-04:00","Edit","feea9e80-3d3f-4f45-b58d-275e4845bcde, 31-Dec-69 16:00:01","Database","ModifyDate, Revision","xs:dateTime","xs:dateTime","Revision","xs:long","xs:long","TemplateDataSet","Edit","\PIProperties","xs:hexBinary","xs:hexBinary"
"ff041b3b-ca18-4909-a16a-18444d8b17b9","1","piadmin","1711669167","2024-03-28T19:39:27-04:00","Edit","feea9e80-3d3f-4f45-b58d-275e4845bcde, 31-Dec-69 16:00:01","Database","ModifyDate, Revision","xs:dateTime","xs:dateTime","Revision","xs:long","xs:long","TemplateDataSet","Edit","\PIProperties","xs:hexBinary","xs:hexBinary"

So lets talk about it
First thing some of your XML Paths are slightly wrong
Example

$_.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Value.Before.Type[0]

Returns

xs:dateTime

What you wanted was more like

$_.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Value.Before[0].'#text'

Returns

2024-03-28T17:47:42-04:00

There seems to be a couple that are like that which just need a slightly adjusted path. But i do not have time to fix those right now.

So why did your script not work?

Lets look at a sample of the code

foreach ($auditRecord in $xmlContent.SelectNodes("//AuditRecord")) {
    $record = New-Object PSObject -Property @{
        AuditRecordID = AuditRecordID
        UserID = PIUser.UserID
    }
}

I would have expected your code to error out above

You are looping XML //AuditRecord using the var $auditRecord

But when you get into the loop you dont call $auditRecord when setting the value of your PSObject properties.

I would have expected to see was more like

foreach ($auditRecord in $xmlContent.SelectNodes("//AuditRecord")) {
    $record = New-Object PSObject -Property @{
        AuditRecordID = $auditRecord.AuditRecordID
        UserID = $auditRecord.PIUser.UserID
    }
}