Our lite solution to 50K sitemap XMLs

Sitemaps can be defined as an individual service. It is possible to generate sitemaps on demand and return instantly without storing XML files on disk. In this method, with any request, a list of expected results will return, but due to the processing cost, it is placed behind a reverse proxy which is handling the request cache as well as routing.

        location ~ ^/(stylesheet|sitemap|products|posts).xml {

            proxy_buffering        on;
            proxy_cache            STATIC;
            proxy_cache_valid      200  1h;
            proxy_cache_use_stale  error timeout invalid_header updating
                                   http_500 http_502 http_503 http_504;


                proxy_set_header Host $host;
                proxy_set_header X-Real-IP $remote_addr;
                proxy_set_header X-Forwarded-For $remote_addr;
                set $target http://sitemap:80;
                proxy_pass $target;
        }

The C# code below will generate the sitemaps.

class Sitemap
{
    public override string ToString()
    {
 if (_tag == "style")
            return """
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet
        version="2.0"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
        xmlns:sitemap="http://www.sitemaps.org/schemas/sitemap/0.9"
        xmlns:image="http://www.google.com/schemas/sitemap-image/1.1"
        xmlns:video="http://www.google.com/schemas/sitemap-video/1.1"
        xmlns:xhtml="http://www.w3.org/1999/xhtml">
...");
        foreach (var item in Items)
        {
            b.Append($"  <url><loc>{item.Loc}</loc></url>");
        }
        b.Append("</urlset>");
        return b.ToString();
    }

    public Sitemap(string Url, string Tag = "sitemap")
    {
        _url = Url;
        _tag = Tag;
    }

    private string _url;
    private string _tag;
    public List<Item> Items{get;set; }

}

class Item
{
    public override string ToString()
    {
        return $@"
        <loc>{Loc}</loc>
        <lastmod>{LastModifiedDate}</lastmod>
";
    }
    public string ? Loc { get; set; }
    public string ? LastModifiedDate { get; set; }
}

internal class Program
{
    public static string Url = "https://www.example.org"
    public static string ConnectionString = "server=mysql;uid=root;pwd=toor;database=db;CharSet=utf8mb4;";

    private static void Main(string[] args)
    {
        var builder = WebApplication.CreateBuilder(args);
        var app = builder.Build();

        app.MapGet("/sitemap.xml", () => Results.File(Encoding.UTF8.GetBytes(new Sitemap(Url, "main").ToString()), "text/xml"));

 app.MapGet("/stylesheet.xml", () => Results.File(Encoding.UTF8.GetBytes(new Sitemap(Url, "style").ToString()), "text/xml"));

        app.MapGet("/posts.xml", () => Results.File(Encoding.UTF8.GetBytes(Fetch(@"
select ...
limit 50000
").ToString()), "application/xml"));

        app.Run();
    }

    private static Sitemap Fetch(string Command)
    {
        MySqlConnection connect = new MySqlConnection(ConnectionString);
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.Connection = connect;
        cmd.CommandText = Command;
        connect.Open();
        var output = new Sitemap(Url);
        try
        {
            MySqlDataReader reader;
            reader = cmd.ExecuteReader();
            output.Items = new List<Item>();
            while (reader.Read())
            {
                var loc = Url + "/" + reader["link"].ToString();
                var lastModifiedDate = reader["updated_at"].ToString();
                output.Items.Add(
                    new Item
                    {
                        Loc = loc,
                        LastModifiedDate = lastModifiedDate
                    });
            }
            reader.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            if (connect.State == ConnectionState.Open)
            {
                connect.Close();
            }
        }
        return output;
    }

}