As you would have noticed off late there are
many sites, which provide RSS feeds of their site content. If you have a close
look you can find that they would be using either an .aspx page or an .asp page
etc., Why do this in a round about manner when there are options for generating
your RSS feed from SQL Server itself. In this article I have explained about RSS
feed and the way to create it through SQL Script directly.
RSS is a method of distributing links to content
in your web site that you'd like others to use. RSS stand for "Rich Site
Summary." Or "Really Simple Syndication." The main part of an RSS
file is the "items" tag. Your RSS file will have to include at least one item
tag and they are generally web pages that you'd like others to link to.
For instance, let's say you run a
technical web site and update the site once in a week. Now you want to share the
information about the top 10 articles to the outside world. How do we do that?
Just create a RSS file and form multiple item tags with information about those
articles.
To enter your item into the RSS
file, you'll need this information:
- Title
- Publication date
- Description
- Link
Find
below a sample:
<item>
<title>title
1</title>
<pubdate>04/19/2004</pubdate>
<description>description
1</description>
<link>http://www.yourwebsite.com/content.aspx?n=1</link>
</item>
<item>
<title>title
2</title>
<pubdate>04/18/2004</pubdate>
<description>description
2</description>
<link>http://www.yourwebsite.com/content.aspx?n=2</link>
</item>
|
After defining “items” which we
would like to distribute to the world, we need to now define our site as a
"channel." We'll use the same tags as with the items: title, description, link
and along with it a new tag called language. But, this time the information will
be about your entire site, rather than a particular portion. That means your
channel information would look something similar to
this:
<title>YourSiteJust
Published</title>
<description>
Small description about your site
...</description>
<link>http://www.yourwebsite.com</link>
<language>en-us</language>
|
As long as we don't surround this information
with an opening and closing <item> tags, it won't be seen as item
information but rather as channel information. With this brief introduction
about RSS let us have a look at the sample RSS feed which we would be creating
during the course of this article.
<?xml version="1.0" encoding="utf-8"
?>
<channel>
<title>YourSiteJust
Published</title>
<description>
Small description about your site
...</description>
<link>http://www.yourwebsite.com</link>
<language>en-us</language>
<item>
<title>title
1</title>
<pubdate>04/19/2004</pubdate>
<description>description
1</description>
<link>http://www.yourwebsite.com/content.aspx?n=1</link>
</item>
<item>
<title>title
2</title>
<pubdate>04/18/2004</pubdate>
<description>description
2</description>
<link>http://www.yourwebsite.com/content.aspx?n=2</link>
</item>
</channel>
|
Create table [dbo].[articlemaster]
(
[articleid] [int] identity (1, 1) not null
,
[articlename] [varchar] (100)
null,
[articledesc] [varchar] (500)
null,
[publisheddate] [smalldatetime] null
) On [primary]
Insert into ArticleMaster Values ('title
1','description 1',getdate())
Insert into
ArticleMaster Values ('title 2','description
2',getdate()-1)
Insert into ArticleMaster
Values ('title 3','description
3',getdate()-2)
Insert into ArticleMaster
Values ('title 4','description
4',getdate()-3)
After creating the sample table and populating
it with some test data, create the following stored procedure. This SP would
generate the required data in an xml format.
Create Proc
GenerateRssFeed
As
Select
Top 10
Ltrim(Rtrim(AName)) as
title,
Convert(varchar(10),PubDate,101) as
pubdate,
Ltrim(Rtrim(ADesc)) as
description,
'http://www.yourwebsite.com/content.aspx?n=' + Cast(ArticleId AS varchar(5)) as
link
From
ArticleMaster item
For xml auto,
elements
GO
|
sp_makewebtask
@outputfile = 'C:\Rss.xml', ---- Point
1
@query = 'Exec GenerateRssFeed',
-- Put the SP name
here
@templatefile =
'C:\RssFeedTemplate.xml' -- Point
2
|
- @outputfile : This parameter specifies the
output file for the execution of the command.
- @Query: The query that is to be used for
generating the HTML output. This is mostly a SELECT statement or a stored
procedure call.
- @templatefile: This parameter specifies the
pathname of the file to be used as the template for the page the stored
procedure generates. The template can contain HTML tags and/or text that need to
appear on the page in addition to the query results. The stored procedure will
replace each <%insert_data_here%> marker within the page template file
with data returned by an SQL
query.
Point
1: This specifies the full pathname of
the xml document that the stored procedure is to create. Please note that you
can also provide the path, which is mapped, in your IIS. i.e.,
'C:\InetPub\WWWRoot\mywebsite\Rss.xml' is also
valid.
Point
2: This specifies the pathname of the
file to be used as the template for the xml the stored procedure generates. The
stored procedure will replace each <%insert_data_here%> marker within the
xml page template file with data returned by it.
You could see the channel information has been
hard coded in the template file itself. That’s because the information is
going to rarely change. Once in a while if it changes you can edit this template
file alone.
<?xml version="1.0" encoding="utf-8" ?>
<channel>
<title>SSWUGJust Published</title>
<description>The SQL Server World
Wide User Group ...</description>
<link>http://www.sswug.com</link>
<language>en-us</language>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</channel>
|
As of now we saw that the RSS feed is generated
from a SQL table based on a Stored procedure. Wouldn't it be nice if the RSS
feed were regenerated whenever data changes in the specified table? Believe me
it isn't a tough task. Let me explain the same below:
sp_makewebtask
@outputfile = 'C:\Rss.xml',
@query = 'Exec GenerateRssFeed',
@templatefile = 'C:\RssFeedTemplate.xml',
@whentype =
10,
@datachg='TABLE=ArticleMaster
COLUMN=ArticleId,AName,ADesc,PubDate'
|
@whentype specifies when the SQL Server Agent is
to run our Web task which creates the RSS feed. By default it takes the value 1,
which means it has to run the task immediately and delete the task (and stored
procedure that creates the Web page) immediately after execution. That is what
has been happening previously. Actually it takes values from 1 to 10.
You can see that I have specified 10; it
means the stored procedure creates a Web task that the SQL Server executes now
and again whenever a user changes a value within one of the columns listed
within the @DATACHG parameter.
@datachg
is the list of table, which is optional, and column names that trigger execution
of the Web task after changes are made. As said before @datachg is required when
@Whentype is set as 10. Please note that specifying the @datachg parameter
creates three triggers Update, Insert & Delete on each table specified by
the @datachg parameter. If a trigger already exists on that table,
sp_createwebtask adds its sp_runwebtask call to the end of the existing trigger
provided the existing trigger was not created WITH ENCRYPTION. If the existing
trigger is encrypted, sp_makewebtask will
fail.
Note:@procname
is the attribute, which specifies the name of the
Web task. Its not an mandatory one, so if omitted, the system will generate the
name as
Web_<YYMMDDHHMMSS><SPID>.
You could cross check that by expanding your database in enterprise manager and
then click on “Stored Procedures”. If at all you have run the
scripts provided in this article you would see a stored procedure, which starts
with a name “Web_”.
On the flip side, we can make use of SQL Web
Assistant Wizard for creating these scripts automatically for us. Let me list
down the steps involved in doing the
same.
Step1:
Open SQL Enterprise manager and choose a
database
Step2: Click on Tools >>
Wizard option
Step3: There would be a
tree structure displayed in the popup window. Expand the node
“Management” and choose “Web Assistant Wizard” before
clicking on “OK”
Now you
could see the wizard in action. It’s pretty straightforward wizard, choose
your options and say finish. In the last screen there would be a provision for
saving the code as a script (*.sql) file. Do that and go through the code for
better understanding.
Hope this article gave some insight into the way
RSS feed can be generated from SQL Server itself. I suggest you fiddle with
@whentype a bit to get familiarize with the other values also. Please note that
for sp_makewebtask to execute properly you need to have admin privileges in the
database. Feel free to ping me @ vmvadivel@yahoo.com
for any further discussion about this.