Create sitemap from the content of the CommerceTools database

Question

I need to create the sitemap file of my CommerceTools based shop and it would be great if it could be done automatically from the contents of the CTP database.

Do you know if there is a module, tool or extension already developed that allows this task?

EDIT->

I am aware that each online store can be built with a different technology. In our specific case, the front-end is based on Sunrise for JVM, so it would be convenient for this tool to be created for this technology, although it is not essential.

I also recognize that each project can have its specific features that make it different from any other (mainly static content or from an external CMS) so I understand that creating a universal tool is very complex.

Anyway I think it would be great to have some tool that could be able to create a "sitemap-products.xml" from the most dynamic content of CTP using the slug of categories and products.

Then this "sitemap-products.xml" could be called from a sitemapindex from which you link both this and other secondary sitemaps that can be self-generated by the CMS (if you have it) and / or other more static that can be created and maintained manually by the development team.

<-EDIT

Thanks in advance.

score 2 · Answer 1 · answered Mar 08 '18 at 03:36

I will give you a simple rule for creating a perfect sitemap from the database.

Sitemap.php :

<?php
$site = "https://yourdomain.ccom/"; // your URL addres with slash at end "/".
$chfreqprod = "weekly"; // the frequency of sitemaps
$priority = "0.8";      // priority
$date = date("Y-m-d\TH:m:s+02:00", time());
define ('DB_USER', 'changeWithYourUser');
define ('DB_PASSWORD', 'changeWithYourPassword');
define ('DB_HOST', 'localhost');
define ('DB_NAME', 'cangeWithYourDataBase');
$conn = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die("Could not connect to the database."); 
        mysql_select_db(DB_NAME, $conn) or die("Can not select the table in the database!");  
        header("Content-Type: text/xml;charset=utf-8");
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<?xml-stylesheet type=\"text/xsl\" href=\"smap.xsl\"?>
<urlset xmlns=\"http://www.sitemaps.org/schemas/sitemap/0.9\">";
$query = @mysql_query("SELECT * FROM products LIMIT 0,25000");
while($row = @mysql_fetch_array($query)){
$product = $row['product_seo'];
echo "<url>
     <loc>".$site.$product.".html</loc>
     <lastmod>".$date."</lastmod>
     <changefreq>".$chfreqprod."</changefreq>     
     <priority>".$priority."</priority>
     </url>";
}
echo "</urlset>";
?>

smap.xsl :

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" 
                xmlns:html="http://www.w3.org/TR/REC-html40"
                xmlns:sitemap="http://www.sitemaps.org/schemas/sitemap/0.9"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="html" version="1.0" encoding="UTF-8" indent="yes" />
    <xsl:template match="/">
        <html xmlns="http://www.w3.org/1999/xhtml">
            <head>
                <title>XML Sitemap</title>
                <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
                <meta name="robots" content="noindex,follow" />
<style type="text/css">
body {
    font-family:"Lucida Grande","Lucida Sans Unicode",Tahoma,Verdana;
    font-size:13px;
}
#intro {
    background-color:#CFEBF7;
    border:1px #2580B2 solid;
    padding:5px 13px 5px 13px;
    margin:10px;
}
#intro p {
    line-height:16.8667px;
}
#intro strong {
    font-weight:normal;
}
table {
    width:100%;
    }
td {
    font-size:11px;
}
th {
    text-align:left;
    padding-right:30px;
    font-size:11px;
    background-color:#E1E3EE;
}
tr.high {
    background-color:whitesmoke;
}
tr:hover {
    background-color:#E8EAF2;
}
#footer {
    width:100%;
    padding:2px;
    margin-top:10px;
    font-size:8pt;
    color:gray;
    text-align:center;
}
#footer a {
    color:gray;
}
a {
    color:#000;
    text-decoration:none;
}
a:hover {
    text-decoration:underline;
}
</style>
</head>
<body>
<xsl:apply-templates></xsl:apply-templates>
</body>
</html>
</xsl:template>

<xsl:template match="sitemap:urlset">
<h1 align="center">XML Sitemap</h1>

        <div id="content">
            <table cellpadding="5">
                <tr style="border-bottom:1px black solid;">
                    <th width="70%">URL</th>
                    <th width="5%">Priority</th>
                    <th width="12%">Change frequency</th>
                    <th width="13%">Last modified</th>
                </tr>
                <xsl:variable name="lower" select="'abcdefghijklmnopqrstuvwxyz'"/>
                <xsl:variable name="upper" select="'ABCDEFGHIJKLMNOPQRSTUVWXYZ'"/>
                <xsl:for-each select="./sitemap:url">
                    <tr>
                        <xsl:if test="position() mod 2 != 1">
                            <xsl:attribute  name="class">high</xsl:attribute>
                        </xsl:if>
                        <td>
                            <xsl:variable name="itemURL">
                                <xsl:value-of select="sitemap:loc"/>
                            </xsl:variable>
                            <a href="{$itemURL}">
                                <xsl:value-of select="sitemap:loc"/>
                            </a>
                        </td>
                        <td>
                            <xsl:value-of select="concat(sitemap:priority*100,'%')"/>
                        </td>
                        <td>
                            <xsl:value-of select="concat(translate(substring(sitemap:changefreq, 1, 1),concat($lower, $upper),concat($upper, $lower)),substring(sitemap:changefreq, 2))"/>
                        </td>
                        <td>
                            <xsl:value-of select="concat(substring(sitemap:lastmod,0,11),concat(' ', substring(sitemap:lastmod,12,5)))"/>
                        </td>
                    </tr>
                </xsl:for-each>
            </table>
        </div>
        <div id="footer">Index Sitemap by <a href="https://www.adydev.com" target="_blank">www.adydev.com</a></div>
    </xsl:template> 

<xsl:template match="sitemap:sitemapindex">
<h1 align="center">XML Sitemap Index</h1>

<div id="content">
<table cellpadding="5">
    <tr style="border-bottom:1px black solid;">
        <th width="85%">URL of sub-sitemap</th>                
        <th width="15%">Last modified</th>
    </tr>
<xsl:for-each select="./sitemap:sitemap">
    <tr>
<xsl:if test="position() mod 2 != 1">
<xsl:attribute  name="class">high</xsl:attribute>
</xsl:if>
        <td>
<xsl:variable name="itemURL">
<xsl:value-of select="sitemap:loc"/>
</xsl:variable>
<a href="{$itemURL}">
<xsl:value-of select="sitemap:loc"/>
</a>
        </td> 
        <td>
<xsl:value-of select="concat(substring(sitemap:lastmod,0,11),concat(' ', substring(sitemap:lastmod,12,5)))"/>
        </td>
    </tr>
</xsl:for-each>
</table>
</div>
<div id="footer">Index Sitemap by <a href="https://www.adydev.com" target="_blank">www.adydev.com</a></div>
</xsl:template>
</xsl:stylesheet>

.htaccess :

RewriteRule ^sitemap.xml$ sitemap.php [L]

For multilanguage sitemap, index sitemap and automate sitemap, please contact me. Thank you!

Thankyou very much @adrian-neațu, but your solution is not what I'm looking for. My fault for not specifying the database model in the title. In my case it is CommerceTools on which you can not perform direct querys but everything has to be done through API calls. As I said before, thank you very much for trying. — MiguelHoz, Mar 08 '18 at 10:22

score 1 · Answer 2 · answered Mar 08 '18 at 09:35

There is no standard module or extension available; the sitemap is frontend-specific since everybody has different URL patterns and non-commerce content on the site. A sitemap needs to be built fitting to the frontend technology your project is developed in.

score 1 · Accepted Answer · answered Sep 24 '18 at 15:49

I have returned to this question to tell you that we finally managed to solve our need by using a module for Play Framework that is precisely capable of generating sitemaps using the URLs that you pass.

We have downloaded the module from the repository of its creators (https://github.com/edulify/play-sitemap-module.edulify.com) and, after configuring some different providers for products, categories and static pages, since we wanted each type of link to have a different refresh frequency and priority for search engines, we have managed to generate our sitemap.xml automatically every 24h.

If someone needs help to implement this funcionality in your store with Sunrise, contact me and I will try to help you.

Thank you very much to all for trying to help us. Greetings. Miguel

Create sitemap from the content of the CommerceTools database

3 Answers3