Create Sitemap for S3 Bucket

This article provides an approach to create a sitemap for files stored in an Amazon Web Services S3 bucket using Invantive SQL.


Perform the following steps to create a sitemap:

  • Install and configure the AWS CLI.
  • Retrieve all objects in a bucket using the command prompt:
aws s3api list-objects --bucket >c:\temp\name.json
  • Log on to Invantive SQL using any provider (use Dummy when you can’t choose).
  • Run the following query:
select xmlformat
       ( '<?xml version="1.0" encoding="UTF-8"?>'
         || '<urlset xmlns="" xmlns:xsi="" xsi:schemaLocation="">'
         || xml
         || '</urlset>'
from   ( select listagg
                ( '<url><loc>' 
                  || xmlencode( 
                  || '</loc><lastmod>' 
                  || xmlencode(dta.lastmodified) 
                  || '</lastmod></url>'
                , null
         from   read_file_text@Os('c:\temp\name.json') rft
         join   jsontable
               ( 'Contents.[*]'
                 passing rft.file_contents
                 columns lastmodified       datetime path 'LastModified'
                 ,       name               varchar2 path 'Key'
               ) dta
         on    name like '%%' /* Insert filter here to only include specific folders. */
  • Either copy the contents to a sitemap.xml file or use the Invantive Script statement ‘local export documents’ to generate the file.