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.

Instructions

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 www.name.nl >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="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">'
         || xml
         || '</urlset>'
       )
       sitemap_contents
from   ( select listagg
                ( '<url><loc>https://www.name.nl/' 
                  || xmlencode(dta.name) 
                  || '</loc><lastmod>' 
                  || xmlencode(dta.lastmodified) 
                  || '</lastmod></url>'
                , null
                ) 
                xml
         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.