How to generate a sitemap from database

[myWebmaster]

I want to be able to generate a new sitemap xml file everytime new records are added to the database.
I use a trigger which make a HTTP get request to fetch data from an API endpoint. I managed to get get the list ok and store in a property like this:

[

{
“sitemapUrl”:“https://www.domain.com/item?mps-ruled-by-iron-fist&id=6”
},
{
“sitemapUrl”:“https://www.domain.com/item?new-naira-scarcity-ph-entrepreneurs-divided-on-cbn-decision&id=4”

}
]

But I dont know how to insert each item inside an XML file like this:

<?xml version="1.0" encoding="UTF-8"?> <-url> <-loc>https://www.domain.com/item?mps-ruled-by-iron-fist&id=6<-/loc> <-/url> <-url> <-loc>https://www.domain.com/item?new-naira-scarcity-divided-on-cbn-decision&id=4<-/loc> <-/url> <-/urlset>

I also need to save the output as a sitemap.xml file and then upload it to github.

If there is another approach, happy to hear suggestions.

Thanks

[MutedJam]

Hi @myWebmaster, have a look at the sample workflow at Create an RSS feed based on a website's content | n8n workflow template. This creates an RSS feed, but sitemaps use a very similar (even simpler) XML structure. So you should be able to adapt this to suit your needs.

[myWebmaster]

Thanks @MutedJam and will give it ago…It would probably take weeks of trial and error to get it to work. I have very little knowledge of javascript or programming

Any help you can offer in adapting it, would be much appreciated. Thanks

[MutedJam]

I think this should do the job @myWebmaster:

This example first mocks the data you already have. It then applies the escaping required for sitemaps and then wraps everything under the required tags from sitemaps.org - Protocol before returning the result.

Hope this helps!

[myWebmaster]

That was very helpful indeed. I really appreciate it very much. Hope some day I can return the favour. Thanks

[myWebmaster]

on a follow up question. I added the module “move binary data” so I can convert the output in to a file. and this is what i get’

"<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n\n/hada/item?ukraine-defense-minister-expects-help-from-western-warplanes&id=5298\n/hada/item?ancer-wraps-up-wire-to-wire-saudi-international-success&id=5299\nhada/item?northeast-temperatures-soar-a-day-after-bone-numbing-cold&id=5300\n\n"

As you can see there are some unexpected opening and closing double quotation marks as well as extra backslashes (like this version=\“1.0\”) and also the \n as well, not required.

How do I get rid of them please?

[MutedJam]

Hi @myWebmaster, that’s most likely because the “Use Raw Data” option isn’t enabled, in which case n8n would apply the JSON.stringify() method:

So perhaps you want to give that option a go? The previous example workflow including the Move Binary Data node could look like so:

[myWebmaster]

That is fantastic. Once again, I appreciate your time for your help. You are a star!