Building Your Own XML Sitemap From a Table in Python and SQL

If a site is built from a database, its sitemap should be too. The file is a derived view of a table: published pages, their addresses and their last-modified dates. A generator that reads the table and streams the XML keeps the sitemap correct by construction, as long as it handles three things that hand-written generators get wrong: escaping, the 50,000-URL limit and the dates.

The table

create table pages(id serial primary key, path text, lastmod date, published boolean default true);
insert into pages(path, lastmod, published)
  select '/library/post-' || g, date '2026-01-01' + (g % 300), g % 40 <> 0 from generate_series(1, 120000) g;
insert into pages(path, lastmod) values ('/search?q=a&b=2', '2026-10-01'), ('/library/café', null);

120,000 rows, 3,000 of them unpublished (every 40th), and two rows added to stress the writer: a path whose query string contains an ampersand, and one with an accented letter and no date. The generator should list the 117,002 published rows: 117,000 of the generated rows plus the two odd ones.

Streaming writer

A sitemap of 50,000 URLs is about 4.5 MB of XML, which is small enough to build in memory. A site with millions of pages has many such files, and the writer should not hold them all. lxml's xmlfile writes elements as you go:

NS = "http://www.sitemaps.org/schemas/sitemap/0.9"
LIMIT = 50000
cur.execute("select path, lastmod from pages where published order by id")
for loc, lm in cur:
    if n % LIMIT == 0:
        if f: el.__exit__(None, None, None); xf.__exit__(None, None, None); f.close()
        part += 1; path, f, xf, w, el = open_part(part); files.append(path)
    with w.element("url"):
        with w.element("loc"): w.write("https://example.test" + loc)
        if lm:
            with w.element("lastmod"): w.write(lm.isoformat())
    n += 1

Each row opens a url element, writes the loc (escaped by the library) and, if there is a date, a lastmod. After every 50,000 rows the file is closed and a new one opened. The open_part helper writes the declaration and the urlset element with the sitemap namespace as the default. The run:

117002 urls into 3 files in 0.58s
sitemap-1.xml 4489277 bytes, 164241 gzipped
sitemap-2.xml 4502608 bytes, 164136 gzipped
sitemap-3.xml 1547254 bytes, 56563 gzipped

117,002 URLs in three files of 50,000, 50,000 and 17,002, in 0.58 seconds on one core, with the rows streamed from the database. The files are 4.5 MB and compress to about 164 KB (27 to 1), which is why the protocol's 50 MB uncompressed limit is almost never the constraint, and 50,000 URLs is. Serve the .gz files with the right content type and list them in the index.

The index

idx = etree.Element("sitemapindex", nsmap={None: NS})
for p in files:
    s = etree.SubElement(idx, "sitemap")
    etree.SubElement(s, "loc").text = f"https://example.test/{p}.gz"
    etree.SubElement(s, "lastmod").text = date.today().isoformat()

The index has one sitemap per file, with its location and a lastmod. Set the index lastmod for each file to the newest lastmod among its URLs, which is more informative than today's date, and only changes when a page in that file changes. Regenerating all files nightly with fresh dates in the index tells a crawler that everything changed, and it will not trust the next claim.

Validation

Download the schemas once and validate every file:

xmllint --noout --schema sitemap.xsd sitemap-1.xml
xmllint --noout --schema siteindex.xsd sitemap-index.xml

sitemap-1.xml validates
sitemap-3.xml validates
sitemap-index.xml validates

All pass. Make this a step of the generation job, because it catches a bug you would otherwise find from a search engine's console: an element in the wrong place, a missing namespace, a date in the wrong format. The official XSD checks structure and that lastmod is a valid date or datetime. It does not check that loc is a good URL, and my test row proves it:

<loc>https://example.test/library/café</loc>

The path with the accented character was written as raw UTF-8, the file validated, and the protocol says URLs must be escaped as they would be in a URL (percent-encoded UTF-8: caf%C3%A9). The XSD sees a string. The writer's XML escaping and the URL's percent-encoding are two different layers, and the library handles only the first. Encode the path yourself (urllib.parse.quote(path, safe="/?&=%") for a path that is already structured; I did not run that line), and put a test on it: every loc must be pure ASCII, begin with the site's scheme and host, and contain no space.

The ampersand in /search?q=a&b=2 was written as & by the library, which is the XML layer done right. Also: do not list pages like that one. A site search result is not a page to submit, and the protocol's examples are canonical URLs. The generator's where published clause should also exclude noindex pages, redirects and non-canonical URLs, which are columns in the real table.

The SQL-only version

PostgreSQL can write the same XML:

select xmlserialize(document xmlelement(name urlset, xmlattributes('http://www.sitemaps.org/schemas/sitemap/0.9' as xmlns),
  xmlagg(xmlelement(name url, xmlelement(name loc, 'https://example.test' || path),
    case when lastmod is not null then xmlelement(name lastmod, lastmod) end))) as text)
from (select path, lastmod from pages where id in (120001, 120002, 1)) s;

<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"><url><loc>https://example.test/library/post-1</loc><lastmod>2026-01-02</lastmod></url><url><loc>https://example.test/search?q=a&amp;b=2</loc><lastmod>2026-10-01</lastmod></url><url><loc>https://example.test/library/café</loc></url></urlset>

The same three rows, the same escaping, and the page with no date has no lastmod element (the case returns null, which xmlagg skips). This is a good fit for a small site and for a database that already produces the pages. It builds the document in memory on the server, so for 50,000 rows it is fine and for millions you split by limit and offset, or by key ranges, and write each result to a file. The Python writer keeps the file handling, gzip and index in one place.

Counting and checking

The first file has 50,000 url elements, and grep -c "<url>" sitemap-1.xml printed 1: the writer produces the whole file on one line, and grep counts lines. Count with the parser (xmllint --xpath 'count(//*[local-name()="url"])' sitemap-1.xml, which has to deal with the default namespace), and compare the sum over the files with the count(*) of the query. After each generation, also fetch a sample of the URLs and check for a 200 status and a canonical tag that points to themselves; a sitemap of redirects is a bad sitemap.

Related reading

For reading sitemaps see reading a sitemap XML file and parsing an XML sitemap into a table; for auditing one, analyzing sitemap data with SQL. The format and limits are at sitemaps.org, and Google's guidance in its sitemap documentation. Test a path in the XPath tester.

When a sitemap generator already exists

Your CMS or framework has one. WordPress, Laravel packages and static site generators produce correct sitemaps. Configure them before writing your own.

The site is small and static. A hand-edited file with fifty URLs is fine, as long as it is validated.

The rules are complicated. If publication logic lives in application code rather than the table, generate from the application's view of the data, not from a copy of the rules in SQL.

You also need images, videos or alternate languages. Those use extension namespaces; add them to the writer with their own nsmap, and validate with the extension schemas.