Create a Google Webmaster Sitemap (free code)

January 21, 2008 at 6:09 am 2 comments


Want to know how to easily create a Google sitemap without having to use any other external tools?

It’s easypeasy if you use MS SQL and ColdFusion!

Let’s assume you have a database full of products (11,000 or more) and you need to generate a sitemap so google knows what to index. You wouldn’t want to do that by hand! 

The SQL is very simple

DECLARE @year CHAR( 4 ), @month VARCHAR( 2 ), @day VARCHAR( 2 ), @lastModified CHAR( 10 )SET @year = CONVERT( CHAR( 4 ), YEAR( GETDATE() ) )
SET @month = CONVERT( VARCHAR, MONTH( GETDATE() ) )
SET @day = CONVERT( VARCHAR, DAY( GETDATE() ) )
SET @lastModified = @year + ‘-‘ + REPLICATE( ‘0’, 2 LEN( @month ) ) + @month + ‘-‘ + REPLICATE( ‘0’, 2 LEN( @day ) ) + @day

SELECT 1 AS tag
, NULL AS parent
, NULL AS [url!1]
, http://www.clickfind.com.au/product/listing.cfm?productIdentity=’ + CAST( identity AS VARCHAR( 10 ) ) AS [url!1!loc!element]
, @lastModified AS [url!1!lastmod!element]
, ‘monthly’ AS [url!1!changefreq!element]
, 1 AS [url!1!priority!element]
FROM product
FOR XML EXPLICIT
How easy is that? Just transfer it to ColdFusion, wrap the
<urlset xmlns=”http://www.google.com/schemas/sitemap/0.84“></urlset> tag around it, safe it as a file with cffile.

Obviously you need change the date last modified variable and base that on the change frequently of each individual product.

You’re more than welcome to copy the code and use it, just be courteous and reference back to this article 😉

Sponsored by www.clickfind.com.au

Advertisements

Entry filed under: ColdFusion, Google, MS SQL. Tags: , , , .

Google Webmaster Tools List of SEO companies

2 Comments Add your own

  • 1. Toolbe Webmaster Seo Search Engine  |  January 22, 2008 at 8:04 pm

    nice hack! thanks for posting this.

    Like

    Reply
  • 2. Australian Search Engine  |  January 22, 2008 at 10:39 pm

    Glad to hear you enjoyed it!

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 19 other followers

Archives

Top Rated

Blog Stats

  • 167,579 hits

%d bloggers like this: