Brainsofsteel header

Making a large Google XML sitemap

Subject: Technology

Description: How to generate a Google XML Sitemap using Microsoft Access


submit to reddit Posted by David Caldwell on 01/08/14 at 20:03

Scenario

You have a large website consisting of dynamic pages populated from a MySql database but how do you create a sitemap?

There are many great sites out there that will do the job for you such as http://www.xml-sitemaps.com/ however many of these have a maximum of 500 pages.

Your site is database driven so why not use a database to create the sitemap.

Typical Google Sitemap Format

<?xml version="1.0" encoding="UTF-8"?>

                <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">

                                <url>

                                                <loc>http://www.mysite.com/mypage.php?id=1</loc>

                                                <lastmod>2014-05-10</lastmod>

                                                <changefreq>monthly</changefreq>

                                                <priority>0.5</priority>

                                </url>

                </urlset>

Step One

Go to your phpMyAdmin and download the table your URL indexes are stored in.  Remember to change the semicolon ‘;’ to a comma  ‘,’.

Step Two

Open Microsoft Access and import the CSV file using the text import wizard.

Create a new query and add your table, now create the following expression, changing Field1to the column that contains your indexes, change the URL to your dynamic page and change the date to the present.

Expr1: "<url><loc>http://www.mysite.com/mypage.php?id=" & [Field1] & "</loc><lastmod>2014-08-01</lastmod>changefreq>monthly</changefreq><priority>0.5</priority></url>"

Run the query and you should get your xml formatted correctly.

Step Three

Now save and export your query to text, choose a tab separator and change the text qualifier to none.

Open your exported text file and paste the following to the beginning.

 

<?xml version="1.0" encoding="UTF-8"?>

<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">

 

Paste the following to the end.

 

</urlset>

Step Four

Now save as sitemap.xml and your page is ready to upload and submit to Google.

 

Keywords: Google, Sitemap, XML, Dynamic




comments powered by Disqus

 Subscribe in a reader

RSS Feed

Latest Posts

17/11/15
How to Create a Time Sheet in Excel
15/11/15
Handling time and dates in PHP
03/02/15
How to Enhance Photographs with the Gimp
25/01/15
How to Cook Pigeon
25/01/15
Telling the Time
24/01/15
An Old Travel Card
10/09/14
How to Learn the Nine Times Table
09/08/14
How to Calculate the Direction between two Points
01/08/14
Making a large Google XML sitemap
18/05/14
How to make a pyramid