Creation of an XML Sitemap from SQL Server In ASP NET
About
In the process of designing our Content Management System, we knew that a sitemap file would need to be included to enable fast discovery of content by the various crawlers that search engines use.
As our system already has a database behind it, the obvious choice was to use this data and create the file dynamically.
The system needed to be able to handle lots of pages, so for built in redundancy we have created a sitemap index page which then links into the sitemaps.
XML Sitemap Index
<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<sitemap>
<loc>https://www.claytabase.co.uk/sitemap.xml?Language=EN&Page=1</loc>
<lastmod>2019-07-17</lastmod>
</sitemap>
<sitemap>
<loc>https://www.claytabase.co.uk/sitemap.xml?Language=EN&Page=2</loc>
<lastmod>2019-07-17</lastmod>
</sitemap>
</sitemapindex>
XML Sitemap
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<url>
<loc>https://www.claytabase.co.uk/</loc>
<lastmod>2013-12-13</lastmod>
<changefreq>monthly</changefreq>
<priority>1</priority>
</url>
<url>
<loc>https://www.claytabase.co.uk/About-Us</loc>
<lastmod>2013-12-09</lastmod>
<changefreq>monthly</changefreq>
<priority>1</priority>
</url>
</urlset>
How to generate it
I have split out the code generation into two classes for ease of reading, but it could be combined if you like.
The generation is hooked into the page load event, but is fairly simple.
- Set the response content type as text/xml and encoding as utf-8
- Create an XML Text Writer and start writing the document
- Create an SQL connection using a connection string saved in the web config file
- Get all of the possible values from your database and push them into a dataset for reading
- Write the first element (sitemapindex or urlset), this is required as part of the standard, and set the schema
- Start a loop through the dataset
- Write the require element (sitemap or url)
- Write the required attributes for each element
- Clean up and close the stream
Sitemap Index
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml
Partial Class Generate_SitemapIndex
Inherits System.Web.UI.Page
Dim conStr As String = ConfigurationManager.ConnectionStrings("MySqlConnection").ConnectionString
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Response.Clear()
Response.ContentType = "text/xml"
Response.Charset = "Utf-8"
Dim xtwFeed As XmlTextWriter = New XmlTextWriter(Response.OutputStream, Encoding.UTF8)
xtwFeed.WriteStartDocument()
Using con As New SqlConnection(conStr)
Dim com As New SqlCommand("SELECT {SitemapUrl},{SitemapModified} FROM {YourDatabase}", con)
Dim ds As New DataSet, da As New SqlDataAdapter(com)
con.Open()
da.Fill(ds)
xtwFeed.WriteStartElement("sitemapindex")
xtwFeed.WriteAttributeString("xmlns", "http://www.sitemaps.org/schemas/sitemap/0.9")
Dim dr = ds.Tables(0).CreateDataReader
While dr.Read
xtwFeed.WriteStartElement("sitemap")
xtwFeed.WriteElementString("loc", dr.Item(0).ToString) 'OR full URL from your database!
xtwFeed.WriteElementString("lastmod", dr.Item(1).ToString) 'ISO1806 format date.
xtwFeed.WriteEndElement()
End While
xtwFeed.WriteEndElement()
End Using
xtwFeed.WriteEndDocument()
xtwFeed.Flush()
xtwFeed.Close()
Response.End()
End Sub
End Class
Sitemap
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml
Partial Class Generate_Sitemap
Inherits System.Web.UI.Page
Dim conStr As String = ConfigurationManager.ConnectionStrings("MySqlConnection").ConnectionString
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Response.Clear()
Response.ContentType = "text/xml"
Response.Charset = "Utf-8"
Dim xtwFeed As XmlTextWriter = New XmlTextWriter(Response.OutputStream, Encoding.UTF8)
xtwFeed.WriteStartDocument()
Using con As New SqlConnection(conStr)
Dim com As New SqlCommand("SELECT {PageUrl},{PageModified},{PageChangeFreq},{PagePriority} FROM {YourDatabase}", con)
Dim ds As New DataSet, da As New SqlDataAdapter(com)
con.Open()
da.Fill(ds)
xtwFeed.WriteStartElement("urlset")
xtwFeed.WriteAttributeString("xmlns", "http://www.sitemaps.org/schemas/sitemap/0.9")
Dim dr = ds.Tables(0).CreateDataReader
While dr.Read
xtwFeed.WriteStartElement("url")
xtwFeed.WriteElementString("loc", dr.Item(0).ToString) 'full URL from your database!
xtwFeed.WriteElementString("lastmod", dr.Item(1).ToString) 'ISO1806 format date.
xtwFeed.WriteElementString("changefreq", dr.Item(2).ToString) 'daily, weekly, monthly etc
xtwFeed.WriteElementString("priority", dr.Item(3).ToString) 0.0 to 1.0
xtwFeed.WriteEndElement()
End While
xtwFeed.WriteEndElement()
End Using
xtwFeed.WriteEndDocument()
xtwFeed.Flush()
xtwFeed.Close()
Response.End()
End Sub
End Class
Website design by Claytabase
This is a section of code that has been modified from Ousia Content Management System code, one of the quickest and most optimised systems on the market, part of our website design services.
