In the following examples, the Products and Categories tables from the Northwind database will be joined to generate fictitious product urls. The query below will be modified to generate the desired xml output.
|40||Seafood||Boston Crab Meat||http://yourwebsite/seafood/boston-crab-meat/40|
|60||Dairy Products||Camembert Pierrot||http://yourwebsite/dairy-products/camembert-pierrot/60|
The following script generates xml sitemaps with the optional tags (lastmod, changefreq, priority) but without validation namespaces. The
FOR XML clause is used in conjunction with the
To enable validation, 2 additional headers must be added to the sitemap urlset node. This requires a slightly different approach with some string handling to get the proper xsi prefix for schemaLocation.
While the output now conforms to xml sitemap protocol, it is a good practice to limit the number of records that goes in a single file (generally less than 10000 at the time of writing). In real life scenarios where the number of records is high, this would give rise to multiple sitemap files which have to be referenced inside a sitemap index file. One way to achieve this is to add support for pagination inside the sql query - one parameter for the starting record number and another for the number of records to be returned (using OFFSET/FETCH NEXT in SQL Server 2012 and above, or a common table expression with
ROW OVER in previous versions).
The client can then loop and create a sitemap file and its corresponding entry into a sitemap index file until no more records are returned.