Creating a Google XML Sitemap using Oracle APEX. #JoelKallmanDay

Oracle APEX makes it easy to build modern web apps, but ensuring that your public pages are properly indexed by Google sometimes requires a little extra work. Recently, I was asked to generate a dynamic XML Sitemap for one such APEX app, and here’s how I went about it.

What is a Google Sitemap, and why is it important?

A Google Sitemap is a simple XML file, usually named sitemap.xml, that lists all the important URLs on your site along with metadata like update dates and priority. It helps search engines like Google discover and understand the structure of your site more efficiently, especially pages that may be hard to find via internal links or other navigation. Submitting it through Google Search Console gives you visibility. In short, it is a practical means to ensure faster, more reliable indexing of your site.

Our APEX application

For simplicity, we’ll ignore the non-essential parts and reduce the app to its three main pages.  The pages that we want to appear on the Google XML Sitemap are the detail pages. We want to be sure that each one of our thousands of articles are indexed.

• A home page
• A search page
• A detailed page for each entry in the database (1,000s of entries)

Understanding the XML structure

The first step was to check Google’s documentation for the required XML structure:

Google Sitemap XML documentation

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <url>
    <loc>https://www.example.com/foo.html</loc>
    <lastmod>2022-06-04</lastmod>
  </url>
</urlset>

We quickly realized that, for our use-case, we also needed to include a News Sitemap:

Google News Sitemap documentation

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"
    xmlns:news="http://www.google.com/schemas/sitemap-news/0.9">
  <url>
    <loc>http://www.mysite.org/ords/r/press/articles?p_slug=companies-a-b-in-merger-talks</loc>
    <lastmod>2022-06-04</lastmod>
    <news:news>
      <news:publication>
        <news:name>The Example Times</news:name>
        <news:language>en</news:language>
      </news:publication>
      <news:publication_date>2008-12-23</news:publication_date>
      <news:title>Companies A, B in Merger Talks</news:title>
    </news:news>
  </url>
</urlset>

Our Data Source: vw_articles View

For the purposes of this blog, I’ve replaced the actual view that we used with a view called vw_articles containing generated demo data. The view returns a list of news articles along with metadata such as their unique IDs, titles, publication dates, last update dates, etc.  A field called url_slug contains a descriptive but also unique, alternate key in a readable text form (e.g., tree-falls-in-a-forest, prime-minister-slips-on-banana) 

Name             Null?    Type          
---------------- -------- ------------- 
ARTICLE_ID       NOT NULL NUMBER        
TITLE            NOT NULL VARCHAR2(200) 
URL_SLUG         NOT NULL VARCHAR2(200) 
PUBLICATION_DATE NOT NULL DATE          
LAST_MODIFIED    NOT NULL DATE          
AUTHOR                    VARCHAR2(100) 
CATEGORY                  VARCHAR2(50)

Generating the XML per article

Now that we know what XML structure Google expects, let’s generate it dynamically using SQL. We'll start by generating the XML for each article. This will generate one row for each article. Each row will contain the XML describing that article.

select xmlelement("url",
         xmlelement("loc", 'http://www.mysite.org/ords/r/press/articles?p_slug=' 
                           || a.url_slug),
         xmlelement("lastmod", to_char(a.last_modified, 'YYYY-MM-DD')),
         xmlelement("changefreq", 
                     case
                       when a.last_modified > sysdate-15 
                         then 'daily'
                       when a.last_modified > sysdate-60 
                         then 'weekly'
                         else 'monthly'
                     end),
         xmlelement("priority", 
                     case
                       when a.last_modified > sysdate-15 
                         then '0.8'
                       when a.last_modified > sysdate-60 
                         then '0.6'
                         else '0.4'
                     end),
          xmlelement("news:news",
            xmlelement("news:publication",
              xmlelement("news:name",'The Best Daily News '||a.article_id),
              xmlelement("news:language",'en')
             ),  
            xmlelement("news:publication_date", a.publication_date),
            xmlelement("news:title", a.title)
           )
         )
  from vw_articles a;

You may notice that we’ve set each entry’s change frequency (changefreq) and priority dynamically - they are set based on the number of days since the article was last updated.

The SQL statement above gives us something that looks like this for each entry:

<url>
 <loc>http://www.mysite.org/ords/r/press/articles?p_slug=olympic-athletes-break-three-world-records</loc>
 <lastmod>2025-05-18</lastmod>
 <changefreq>monthly</changefreq>
 <priority>0.4</priority>
 <news:news>
  <news:publication>
   <news:name>The Best Daily News 4</news:name>
   <news:language>en</news:language>
  </news:publication>
  <news:publication_date>2025-05-18</news:publication_date>
  <news:title>Olympic Athletes Break Three World Records</news:title>
 </news:news>
</url>

Aggregating entries into a sitemap

Rather than generating multiple XML fragments, we’d like to aggregate each entry into a single XML document. To do this, we first use the XMLAGG function so that all the entries are returned as a single, aggregated row. We would like the entries to be sorted in descending order by last_modified, so we add order by a.last_modified desc to the end of the XMLAGG function.

We’ll enclose these aggregated entries inside a <urlset> tag to create a complete, valid XML document. Finally, we’ll use xmlserialize to convert our XMLTYPE output to an indented CLOB. We’ve also added the XML declaration at the start of the document.

select '<?xml version="1.0" encoding="UTF-8"?>' || chr(10) ||
   xmlserialize(
     content
     xmlelement(
           "urlset", 
           xmlattributes
              ('http://www.sitemaps.org/schemas/sitemap/0.9' as "xmlns",
               'http://www.google.com/schemas/sitemap-news/0.9' as "xmlns:news"),
           xmlagg(
             xmlelement("url",
               xmlelement("loc", 'http://www.mysite.org/ords/r/press/articles?p_slug=' 
                                 || a.url_slug),
               xmlelement("lastmod", to_char(a.last_modified, 'YYYY-MM-DD')),
               xmlelement("changefreq", 
                          case
                            when a.last_modified > sysdate-15 
                              then 'daily'
                            when a.last_modified > sysdate-90 
                              then 'weekly'
                              else 'monthly'
                          end),
               xmlelement("priority", 
                          case
                            when a.last_modified > sysdate-15 
                              then '0.8'
                            when a.last_modified > sysdate-90 
                              then '0.6'
                              else '0.4'
                          end),
                xmlelement("news:news",
                  xmlelement("news:publication",
                    xmlelement("news:name",'The Very Best Daily News'),
                    xmlelement("news:language",'en')
                   ),  
                  xmlelement("news:publication_date", a.publication_date),
                  xmlelement("news:title", a.title)
                 )
               )
            )
        )
     as clob indent size = 2
   ) as xml_output
from vw_articles a;

 For very large datasets (tens of thousands of URLs), consider chunking your sitemap into multiple sitemap files and generating a sitemap index file, as per Google’s recommendation (maximum 50,000 URLs per sitemap).

Final XML

This gives us our final XML, which is one <urlset> that encompasses all the <url> entries that we want to appear on the sitemap:

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:news="http://www.google.com/schemas/sitemap-news/0.9">
  <url>
    <loc>http://www.mysite.org/ords/r/press/articles?p_slug=climate-change-policy-update</loc>
    <lastmod>2025-08-17</lastmod>
    <changefreq>daily</changefreq>
    <priority>0.8</priority>
    <news:news>
      <news:publication>
        <news:name>The Very Best Daily News</news:name>
        <news:language>en</news:language>
      </news:publication>
      <news:publication_date>2025-08-16</news:publication_date>
      <news:title>Climate Change Policy Update</news:title>
    </news:news>
  </url>
  ...
  ...  thousands of entries
  ...
</urlset>

You can validate your sitemap using the W3C XML Validator or Google’s own testing tools before submitting.

Serving the Sitemap from APEX

1. Create a blank APEX page with a minimal template. Call this new page sitemap with an alias of sitemap. Make sure that this page is a public page.

2. I strongly recommend using packages when developing any substantial piece of PL/SQL. For this example, I’ve created a package that contains the SQL statement to generate the XML shown above. The package is called googlesitemaps and you can download it from here.
   
   Basically, this package's procedure generateMainSitemap selects the XML <urlset> into a CLOB and then outputs that CLOB to the HTTP stream.

   procedure generateMainSitemap is
      l_clob_output  clob;  -- Holds the generated XML sitemap content
   begin
     ...
     ...   (removed for brevity)
     ...   A section that retrieves the XML in a CLOB variable 
     ...   called l_clob_output using the SQL shown earlier.
     ...   You can download this using the link above
     ...
     
     -- Set HTTP response content type to XML
     owa_util.mime_header('application/xml', false);
     
     -- Specify content length for client optimization
     htp.p('Content-Length: ' || dbms_lob.getlength(l_clob_output));
     
     -- Close HTTP headers
     owa_util.http_header_close;
     
     -- Output the XML content without HTML escaping (preserve XML structure)
     apex_util.prn(p_clob => l_clob_output, p_escape => false);

   end generateMainSitemap;

3. Once the package is in place, create a pre-rendering, before-header process called “Output the XML sitemap”. Inside the begin ... end, this process will contain two lines.
   
   The first one calls the googlesitemaps.generateMainSitemap procedure to output the XML directly to the HTTP stream.
   
   The second one calls apex_application.stop_apex_engine to stop all APEX processing dead in its tracks. This ensures that nothing else is output to the HTTP stream.

Submitting the sitemap to the Google Search Console

Now we're ready to submit the sitemap to Google.  
Google will automatically search for a file called "sitemap.xml" but, unfortunately, with the current version of APEX (24.2), we can't add a dot in a page alias.  This means that we can't "pretend" to have a file called "sitemap.xml".  

The workaround to this is to submit your new sitemap directly to Google using the Google Search Console for your site.  The search console looks something like this.

If we click on Sitemaps we’ll come to a page that looks like this:

Here, you simply have to enter the URL of your newly created sitemap page and submit it to Google. It won't be processed immediately, but it will be added to a queue for processing. In my experience, it seems to get processed within two or three days.

Conclusion

So, there we have it. With this approach, you can make your APEX applications more discoverable and ensure your content reaches a wider audience through proper search indexing.  I hope that this blog post helps some of you make your public-facing APEX applications gain a wider audience.

Happy APEXing!!

Making XML tags dynamic in SQL and PL/SQL

While trying to produce XML using Oracle's native XML functions, I needed some of the XML tags to be dynamic.  To simplify and illustrate the problem that I encountered, I'll show an example that uses the time-tested, traditional EMP and DEPT tables.  

Let's say that we need to produce something like this for all departments.

<departments>
 <accounting>
  <employee>Clark</employee>
  <employee>King</employee>
  <employee>Miller</employee>
 </accounting>
 ...
</departments>

Let's start with a short SQL to get an aggregated employee list for each department.  The result is four rows of xmltype - one for each department

 select xmlelement("department", 
           xmlagg(xmlelement("employee", initcap(e.ename))))
   from dept d
   left outer join emp e on (e.deptno = d.deptno)
   group by d.deptno;
Result (4 rows):
<department>
 <employee>King</employee>
 <employee>Miller</employee>
 <employee>Clark</employee>
</department>
<department>
 <employee>Jones</employee>
 <employee>Adams</employee>
 <employee>Smith</employee>
 <employee>Ford</employee>
 <employee>Scott</employee>
</department>
<department>
 <employee>Blake</employee>
 <employee>James</employee>
 <employee>Turner</employee>
 <employee>Martin</employee>
 <employee>Ward</employee>
 <employee>Allen</employee>
</department>
<department>
 <employee></employee>
</department>

Next we'll aggregate these inside a single outer tag called "departments"

 select xmlelement("departments",
           xmlagg(
              xmlelement("department", 
                 xmlagg(xmlelement("employee", initcap(ename))))))
   from dept d
   left outer join emp e on (e.deptno = d.deptno)
  group by d.deptno, d.dname;
Result:
<departments>
 <department>
  <employee>King</employee>
  <employee>Miller</employee>
  <employee>Clark</employee>
 </department>
 <department>
  <employee>Jones</employee>
  <employee>Adams</employee>
  <employee>Smith</employee>
  <employee>Ford</employee>
  <employee>Scott</employee>
 </department>
 <department>
  <employee>Blake</employee>
  <employee>James</employee>
  <employee>Turner</employee>
  <employee>Martin</employee>
  <employee>Ward</employee>
  <employee>Allen</employee>
 </department>
 <department>
  <employee></employee>
 </department>
</departments>

Now we'll try to change the <department> tag to have the value of the actual department name by replacing xmlelement("department",  with xmlelement(dname,   and we'll see that the value of dname doesn't appear

 select xmlelement("departments",
           xmlagg(
              xmlelement(dname, 
                 xmlagg(xmlelement("employee", initcap(ename))))))
   from dept d
   left outer join emp e on (e.deptno = d.deptno)
  group by d.deptno, d.dname;
Result:
<departments>
 <DNAME>
  <employee>King</employee>
  <employee>Miller</employee>
  <employee>Clark</employee>
 </DNAME>
 <DNAME>
  <employee>Jones</employee>
  <employee>Adams</employee>
  <employee>Smith</employee>
  <employee>Ford</employee>
  <employee>Scott</employee>
 </DNAME>
 <DNAME>
  <employee>Blake</employee>
  <employee>James</employee>
  <employee>Turner</employee>
  <employee>Martin</employee>
  <employee>Ward</employee>
  <employee>Allen</employee>
 </DNAME>
 <DNAME>
  <employee></employee>
 </DNAME>
</departments>

So, as we can see, the value of the dname column has not been interpreted and used for the tag.  The query is simply using the string DNAME instead.   The problem now becomes "how can we force our query to use the value of dname as an XML tag?"

And, of course, Oracle have given us a solution - the evalname keyword will tell the query that the expression following it is to be evaluated and that the result of that evaluation should be used as the XML tag.  Armed with this knowledge, we'll now make a small change to the query

 select xmlelement("departments",
           xmlagg(
              xmlelement(evalname lower(dname), 
                 xmlagg(xmlelement("employee", initcap(ename))))))
   from dept d
   left outer join emp e on (e.deptno = d.deptno)
  group by d.deptno, d.dname;
Result:
<departments>
 <accounting>
  <employee>King</employee>
  <employee>Miller</employee>
  <employee>Clark</employee>
 </accounting>
 <research>
  <employee>Jones</employee>
  <employee>Adams</employee>
  <employee>Smith</employee>
  <employee>Ford</employee>
  <employee>Scott</employee>
 </research>
 <sales>
  <employee>Blake</employee>
  <employee>James</employee>
  <employee>Turner</employee>
  <employee>Martin</employee>
  <employee>Ward</employee>
  <employee>Allen</employee>
 </sales>
 <operations>
  <employee></employee>
 </operations>
</departments>

So, as we can see, the value of the expression following the evalname keyword has been used as the XML tag.

Of course, the above example is a simple example to illustrate the use of evalname.

The actual problem that I was solving involved calculating multiple tag values according to a reasonably complex piece of business logic that was implemented via PL/SQL packages.  These tag values were then passed as parameters to the procedure generating the XML.  From there it was easy to just use evalname parameter_name for the dynamic tag names.

The above was run and tested on the Oracle Autonomous Database Cloud using an "Always Free" database that is running database version 19c at the time of writing.

I hope that this is useful for some of you.  Happy XMLing with SQL and PL/SQL!

Ensuring that XMLTYPE to JSON transformations create JSON arrays - even when there is only a single element.

Recently, my colleagues and I were transforming a large quantity of XML to JSON via the APEX_JSON package.  We wanted any XML tag that was repeated more than once to be transformed into a JSON array.  However there was an issue with repeatable tags, whenever there was only one instance of the element then it wasn't created as an array.  

Here's a very simplified example to illustrate the issue.

<parents>
  <parent>
    <name>Aidan</name>
    <children>
      <child>Aoife</child>    <=== two entries
      <child>Fionn</child>
    </children>
  </parent>
  <parent>
    <name>Eamon</name>
    <children>
      <child>Saoirse</child>  <=== a single entry
    </children>
  </parent>
</parents>

When an XML document such as the one above is converted to JSON, the two children of Aidan are represented as an array containing two elements - but the single child of Eamon is not.  

This can cause some issues for JSON parsers consuming the data, as they now have to cater for two scenarios (with-array and without-array) to correctly extract the data. 

[
   {
      "name":"Aidan",
      "children":[         <=== this is what we want, an array.
         "Aoife",
         "Fionn"
      ]
   },
   {
      "name":"Eamon",
      "children":{         <=== we wanted an array here too :(
         "child":"Saoirse"
      }
   }
]

This piece of PL/SQL code illustrates the issue.

set serveroutput on
declare
  v_xml    xmltype;
  v_json   clob;
begin
  v_xml := xmltype(
  '<parents>
      <parent>
        <name>Aidan</name>
        <children>
          <child>Aoife</child>
          <child>Fionn</child>
        </children>
      </parent>
      <parent>
        <name>Eamon</name>
        <children>
          <child>Saoirse</child>
        </children>
      </parent>
    </parents>');

  apex_json.initialize_clob_output;
  apex_json.write(v_xml);
  v_json := apex_json.get_clob_output;
  apex_json.free_output;  
  
  dbms_output.put_line(v_json);
end;
/

[
 {"name":"Aidan",
  "children":["Aoife","Fionn"]},    <=== is an array
 {"name":"Eamon",
  "children":{"child":"Saoirse"}}   <=== is not an array :(
]

We tried several ways to work around this. For example, one unsatisfactory solution that we tried was to create of an empty <child/> tag whenever there was only one entry and then to subsequently strip it out of the resulting JSON.  

However, a quick communication with the ever-responsive APEX Development Team yielded a more elegant solution... 

Apparently, there are some naming conventions used by the DB XML to JSON generators.  Amongst these are :

• if an XML node name is "rowset", then it always maps to an JSON array. 
• if an XML node has a sub-node that ends in "_row", then it also always maps to an JSON array.

Armed with this knowledge, we modified our XML slightly and renamed the <child> tag to <child_row> so that our PL/SQL example now becomes:

set serveroutput on
declare
  v_xml    xmltype;
  v_json   clob;
begin
  v_xml := xmltype(
  '<parents>
      <parent>
        <name>Aidan</name>
        <children>
          <child_row>Aoife</child_row>
          <child_row>Fionn</child_row>
        </children>
      </parent>
      <parent>
        <name>Eamon</name>
        <children>
          <child_row>Saoirse</child_row>
        </children>
      </parent>
    </parents>');

  apex_json.initialize_clob_output;
  apex_json.write(v_xml);
  v_json := apex_json.get_clob_output;
  apex_json.free_output;  
  
  dbms_output.put_line(v_json);
end;
/

[ 
 {"name":"Aidan",
  "children":["Aoife","Fionn"]},  <=== is an array
 {"name":"Eamon",
  "children":["Saoirse"]}         <=== is also an array!!
]

Now we can consistently generate arrays without having to resort to complex pre- and post-processing.  The only little bit of pre-processing that we need to do is ensuring that any XML tags that must become JSON arrays end with "_row".

Many thanks to the great APEX Development Team and especially to Christian Neumueller for the support and help that they gave us for this issue!!

The above was run and tested on the Oracle Autonomous Database Cloud using an "Always Free" database that is running database version 19c at the time of writing.