Migrating log4net config to .NET 6 - XmlHierarchyConfigurator error and logs not being sent to SQL server

Question

Trying to migrate this log4net.config from a .NET Framework solution which had it placed in Web.config, but I am getting a XmlHierarchyConfigurator error in .NET6 and if I remove that stacktrace config line then there are no errors but no logs are received in my SQL Server database. Using the Apache log4net package version 2.0.14

Am I missing something?

Running log4net in debug mode I see these ERRORs:

log4net:ERROR [AdoNetAppender] ErrorCode: GenericFailure. Failed to load connection type [System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0,Culture=neutral, PubliicKeyToken=b77a5c561934e089]
System.IO.FileNotFoundException: Could not load file or assembly 'System.Data.SqlClient, Version=0.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The System cannot find the file specified.
...
at log4net.Util.SystemInfo.GetTypeFromSyring<Assembly relativeAssembly, string typeName, Boolean throwOnError, Boolean ignoreCase>
   at log4net.Appender.AdoNetAppender.ResolveConnectionType<>

log4net.config

<?xml version="1.0" encoding="utf-8"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  https://go.microsoft.com/fwlink/?LinkId=301879
  -->
<configuration>
    <configSections>
        <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
    </configSections>
    <log4net>
        <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
            <bufferSize value="1" />
            <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
            <connectionString value="data source=MyDatabaseSource; initial catalog=MyDatabase;integrated security=true;" />
            <commandText value="INSERT INTO dbo.Log ([Date],[Level],[Logger],[Message],[Exception],[UserName],[IpAddress],[UserAgent],[RequestUrl],[RequestMethod]) VALUES (@date,@level,@logger,@message,@exception,@username,@ipaddress,@useragent,@url,@method)" />
            <parameter>
                <parameterName value="@date" />
                <dbType value="DateTime" />
                <layout type="log4net.Layout.RawTimeStampLayout" />
            </parameter>
            <parameter>
                <parameterName value="@level" />
                <dbType value="String" />
                <size value="50" />
                <layout type="log4net.Layout.PatternLayout">
                    <conversionPattern value="%level" />
                </layout>
            </parameter>
            <parameter>
                <parameterName value="@username" />
                <dbType value="String" />
                <size value="255" />
                <layout type="log4net.Layout.PatternLayout">
                    <conversionPattern value="%aspnet-request{AUTH_USER}" />
                </layout>
            </parameter>
            <parameter>
                <parameterName value="@ipaddress" />
                <dbType value="String" />
                <size value="100" />
                <layout type="log4net.Layout.PatternLayout">
                    <conversionPattern value="%aspnet-request{REMOTE_ADDR}" />
                </layout>
            </parameter>
            <parameter>
                <parameterName value="@useragent" />
                <dbType value="String" />
                <size value="150" />
                <layout type="log4net.Layout.PatternLayout">
                    <conversionPattern value="%aspnet-request{HTTP_USER_AGENT}" />
                </layout>
            </parameter>
            <parameter>
                <parameterName value="@url" />
                <dbType value="String" />
                <size value="4000" />
                <layout type="log4net.Layout.PatternLayout">
                    <conversionPattern value="%aspnet-request{URL} %aspnet-request{QUERY_STRING}" />
                </layout>
            </parameter>
            <parameter>
                <parameterName value="@method" />
                <dbType value="String" />
                <size value="50" />
                <layout type="log4net.Layout.PatternLayout">
                    <conversionPattern value="%aspnet-request{REQUEST_METHOD}" />
                </layout>
            </parameter>
            <parameter>
                <parameterName value="@logger" />
                <dbType value="String" />
                <size value="255" />
                <layout type="log4net.Layout.PatternLayout">
                    <conversionPattern value="%logger" />
                </layout>
            </parameter>
            <parameter>
                <parameterName value="@message" />
                <dbType value="String" />
                <size value="4000" />
                <layout type="log4net.Layout.PatternLayout">
                    <conversionPattern value="%message" />
                </layout>
            </parameter>
            <parameter>
                <parameterName value="@exception" />
                <dbType value="String" />
                <size value="4000" />
                <layout type="log4net.Layout.ExceptionLayout">
                    <conversionPattern value="%stacktrace" /> <!-- error from this line in .NET6, but works in older .NET Framework -->
                </layout>
            </parameter>
        </appender>
        <root>
            <level value="ALL" />
            <appender-ref ref="AdoNetAppender" />
        </root>
    </log4net>
</configuration>

Program.cs

using log4net.Config;

var builder = WebApplication.CreateBuilder(args);

XmlConfigurator.Configure(new FileInfo("log4net.config"));

builder.Services.AddControllersWithViews();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.Run();

HomeController.cs

using log4net;

private static readonly ILog Log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

public string TestMethod() 
{
Log.Error("log message test");
}