Creation of an XML Sitemap from SQL Server In ASP NET

Creation of an XML Sitemap from SQL Server In ASP NET

About

I wanted to create a site map for my website, and decided XML was the best format for the search engines from what I could see on Wikipedia. Along with some other articles I found on the web, I cam up with the code below. It should integrate seamlessly into an application with an inclusion in a robots.txt file.

XML

<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <url>
    <loc>https://www.claytabase.co.uk/en/About</loc>
    <lastmod>2013-12-13</lastmod>
    <changefreq>monthly</changefreq>
    <priority>1</priority>
  </url>
  <url>
    <loc>https://www.claytabase.co.uk/en/Articles</loc>
    <lastmod>2013-12-09</lastmod>
    <changefreq>monthly</changefreq>
    <priority>1</priority>
  </url>
</urlset>
It is very similar to my RSS article where RSS XML is created programmatically, and creates the relevant XML for you.

VB

Imports System.Data.SqlClient

Imports System.Xml

Imports System.Data

Imports System.Data.Sql

Partial Class SiteMap

    Inherits System.Web.UI.Page

    Dim con As New SqlConnection("{YourSQLConnection}")

    Private SubLoadFeed() Handles Me.Load

        Dim d As DateTime = DateTime.Now

       Response.Clear()

       Response.ContentType = "text/xml"

        Dim xtwFeed As XmlTextWriter = NewXmlTextWriter(Response.OutputStream, Encoding.UTF8)

       xtwFeed.WriartDocument()

 

       xtwFeed.WriartElement("urlset")

       xtwFeed.WriteAttriburing("xmlns","https://www.sitemaps.org/schemas/sitemap/0.9")

 

       xtwFeed.WriartElement("url")

       xtwFeed.WriteElementString("loc","https://www.domain.com/Home/")

       xtwFeed.WriteElementString("lastmod",String.Concat(d.ToString("yyyy-MM-dd")))

       xtwFeed.WriteElementString("changefreq","monthly"'always, hourly, daily, weekly, monthly, yearly, never

       xtwFeed.WriteElementString("priority","1")

       xtwFeed.WriteEndElement()

       xtwFeed.WriartElement("url")

       xtwFeed.WriteElementString("loc","https://www.domain.com/About/")

       xtwFeed.WriteElementString("lastmod",String.Concat(d.ToString("yyyy-MM-dd")))

       xtwFeed.WriteElementString("changefreq","monthly")

       xtwFeed.WriteElementString("priority","1")

       xtwFeed.WriteEndElement()

       xtwFeed.WriartElement("url")

       xtwFeed.WriteElementString("loc","https://www.domain.com/Blog/")

       xtwFeed.WriteElementString("lastmod",String.Concat(d.ToString("yyyy-MM-dd")))

       xtwFeed.WriteElementString("changefreq","monthly")

       xtwFeed.WriteElementString("priority","1")

       xtwFeed.WriteEndElement()

       xtwFeed.WriartElement("url")

       xtwFeed.WriteElementString("loc","https://www.domain.com/WebDesign/")

       xtwFeed.WriteElementString("lastmod",String.Concat(d.ToString("yyyy-MM-dd")))

       xtwFeed.WriteElementString("changefreq","monthly")

        xtwFeed.WriteElementString("priority""1")

       xtwFeed.WriteEndElement()

        Dim com As New SqlCommand("SELECT{YourLoc},{YourDateMod},{YourChangeFreq},{YourPriority} FROM{YourDatabase}", con)

       con.Open()

        Dim dr = com.ExecuteReader

        While dr.Read

           xtwFeed.WriartElement("url")

           xtwFeed.WriteElementString("loc","https://www.domain.com/Articles/afdasf/"+ dr.Item(0).ToString) 'OR full URL from yourdatabase!

           xtwFeed.WriteElementString("lastmod",String.Concat(dr.Item(1).ToString("yyyy-MM-dd"))) 'ISO1806format date.

           xtwFeed.WriteElementString("changefreq","monthly"'Or dr.Item(2).ToString

           xtwFeed.WriteElementString("priority","0.5"'Ordr.Item(3).ToString

            xtwFeed.WriteEndElement()

        End While

       dr.Close()

       con.Close()

       xtwFeed.WriteEndElement()

       xtwFeed.Flush()

       xtwFeed.Close()

       Response.End()

    End Sub

End Class

Ousia Logo