TalkApex

How to Generate a sitemap.xml in SQL

Martin Giffy D'Souza's photo
Martin Giffy D'Souza
·

2 min read

I recently had to generate a sitemap.xml for an public facing APEX application. The official sitemap protocol has the following example:

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
   <url>
      <loc>http://www.example.com/</loc>
      <lastmod>2005-01-01</lastmod>
      <changefreq>monthly</changefreq>
      <priority>0.8</priority>
   </url>
</urlset>

I've seen various implementations of this in SQL and PL/SQL that require a lot of code. Thankfully the entire XML file can be generated with one SQL statement. Using the sample emp table the following query shows how it can be generated: note the URLs won't make sense given the emp table doesn't map to any URLs:

select
    xmlserialize(
        content
        xmlroot(
            xmlelement(
                "urlset",
                xmlattributes('http://www.sitemaps.org/schemas/sitemap/0.9' as "xmlns"),
                xmlagg(
                    xmlelement(
                        "url",
                        xmlelement("loc", 'http://my-url.com/' || e.ename),
                        -- See https://www.w3.org/TR/NOTE-datetime for format
                        -- If e.hiredate was a timestamp can use:
                        -- xmlelement("lastmod", to_char(e.hiredate at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')),
                        xmlelement("lastmod", to_char(e.hiredate, 'YYYY-MM-DD')),
                        -- See https://www.sitemaps.org/protocol.html#xmlTagDefinitions for changefreq and other options
                        xmlelement("changefreq", 'daily')
                    )
                ) -- xmlagg
            ) -- urlset
        ,
        -- Verion is only meant for a number but using a trick to also add the "encoding" attribute
        -- See https://forums.oracle.com/ords/apexds/post/how-to-add-version-and-encoding-to-xml-2163#comment_323462166473746547632690547881602100073
        version '1.0" encoding="UTF-8'
        )
        as clob
    ) sitemap_xml
from emp e
;

Result:

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <url>
    <loc>http://my-url.com/KING</loc>
    <lastmod>1981-11-17</lastmod>
    <changefreq>daily</changefreq>
  </url>
<!-- ... (all the other emps) -->
  <url>
    <loc>http://my-url.com/MILLER</loc>
    <lastmod>1982-01-23</lastmod>
    <changefreq>daily</changefreq>
  </url>
</urlset>

For APEX developers see APEX idea FR-3105 has some discussion on how to include a sitemap.xml file in an application. Specifically look at the comment by vladislav.uvarov

SQLOracle