SQL server - generate sitemap table

Have you ever wondered why some of your site's pages are indexed by search engines while others are not? Using a sitemap you can tell search engines exactly where to find each of your site's pages. A sitemap is a file using XML that lists URLs for a site so that the site can be more accurately crawled by search engines.
The best news is that now you can specify the sitemap in your robots.txt file and Google's Webmaster Tools, MSN Live, and Yahoo all allow you to submit a site map URL when listing your site.
Here is an example sitemap.

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
    <url>
        <loc>http://www.dotnetdesignguide.com/</loc>    
        <changefreq>daily</changefreq>
        <priority>0.5</priority>
    </url>
</urlset>

Unless you have a blog or CMS you have to manually update your sitemap 
every time you add a new page to your site.
   Because aspiring web developers never do anything manual we will 
create a dynamic sitemap using ASP.NET and SQL Server. The urls of the
   pages on our site will be stored in a SQL Server table and we'll 
create an ASP.Net page to display the XML syntax. Additionally we could 
take it a 
   step further and create navigation hyperlinks for our site or even 
content specific landing pages using the urls from the table

Step 1. SQL Server Table and Stored Procedure

First we create a table called "mysitemap". Open SQL Server and open a new query window in your database. Cut and paste the following SQL statement below to create the table. This table will store our data about the urls on our webite that we want search engines to find.

CREATE TABLE [dbo].[MySitemap]( [ID] [int] IDENTITY(1,1) NOT NULL, [Location] [varchar](200) NULL, [ChangeFreq] [varchar](20) NULL CONSTRAINT [DF_MySitemap_ChangeFreq] DEFAULT ('daily'), [Priority] [varchar](3) NULL CONSTRAINT [DF_MySitemap_Priority] DEFAULT ((0.5)), [Category] [varchar](50) NULL, CONSTRAINT [PK_MySitemap] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

After adding some data your table should look similar to this with different urls of course.


Next keeping an eye on security and performance we'll create a SQL Server stored procedure called sp_mysitemap_select to query the urls from the table we just created. Be sure to assign execute permissions for this stored procedure to the sql login that your web site uses to query the database.
We are going to add a conditional statement to this stored procedure so that we can use it later on in the example to create navigation menus for the site as well. This way once we have a new page ready on the site all we have to do is add the url to the SQL table and it will be available in the navigation menu as well as the site map automatically.

CREATE PROCEDURE [dbo].[sp_mysitemap_select] ( @category varchar(50) ) AS if (@category is null) begin select Location, ChangeFreq, Priority from mysitemap order by Category end else begin select Location from mysitemap where Category=@category end return

Being good programmers we always test our code on the database before hooking it into a web page so manually execute the store procedure we just created in a query window