Create Rss feed from data in database

[Billy Barth]

I would like to create a RSS feed from my Access Database.
I have an Access table for news which has the article title, date, and
story in it. What I would like to do is pull the data from there into an
xml file to create the RSS feed, so when users upload a news story it
automatically updates in the xml file. Can this be done with asp? and where
should I look for more information. Thanks, This is a great group.

 

[Brett_A]

<?xml version="1.0" encoding="ISO-8859-1"?>
<% Response.Buffer = true
Response.ContentType = "text/xml"

Function ApplyXMLFormatting(strInput)
strInput = Replace(strInput,"&", "&amp;")
strInput = Replace(strInput,"'", "'")
strInput = Replace(strInput,"""", "&quot;")
strInput = Replace(strInput, ">", "&gt;")
strInput = Replace(strInput,"<","&lt;")

ApplyXMLFormatting = strInput
End Function
%>
<rss version="2.0">
<channel>
<title>xxxxxxxxxxxx</title>
<description>xxxxxxxxxxxxx</description>
<link>xxxxxxxxxxxxx</link>
<language>en-us</language>
<copyright>xxxxxxxxxxx</copyright>
<managingEditor>xxxxxxxxxxxx</managingEditor>

<%
Dim rs_blog_feed
Dim rs_blog_feed_numRows

Set rs_blog_feed = Server.CreateObject("ADODB.Recordset")
rs_blog_feed.ActiveConnection = xxxxxxxx
rs_blog_feed.Source = "xxxxxxxxxxxxxxxx"
rs_blog_feed.CursorType = 0
rs_blog_feed.CursorLocation = 2
rs_blog_feed.LockType = 1
rs_blog_feed.Open()

rs_blog_feed_numRows = 0
%>

<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 10
Repeat1__index = 0
rs_blog_feed_numRows = rs_blog_feed_numRows + Repeat1__numRows
%>

<%
While ((Repeat1__numRows <> 0) AND (NOT rs_blog_feed.EOF))
%>
<item>
<title>xxxxxxxxxxx</title>
<description><%=ApplyXMLFormatting(xxxxxxxxxxxxxx)%></description>
<link>xxxxxxxxxxxxxxxxx</link>
<pubDate>xxxxxxxxxxxxxx</pubDate>
</item>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rs_blog_feed.MoveNext()
Wend
%>

<%
rs_blog_feed.Close()
Set rs_blog_feed = Nothing
%>
</channel>
</rss>

 

[Mike Brind]

I would like to create a RSS feed from my Access Database.
I have an Access table for news which has the article title, date, and
story in it. What I would like to do is pull the data from there into an
xml file to create the RSS feed, so when users upload a news story it
automatically updates in the xml file. Can this be done with asp? and
where
should I look for more information. Thanks, This is a great group.

Was used to pull all stories entered within the last 30 days. Obviously you
can alter the date range, regional settings (eg language, date format, time)
and links according to your needs. The result validates against RSS 2.0.

<%
function tidyxml(text)
text = replace(text,Chr(180),"'")
text = replace(text,"&","&amp;")
text = replace(text,"'","'")
text = replace(text,"'","'")
text = replace(text,""","""")
text = replace(text,""","""")
text = replace(text,"<br>","")
text = replace(text,"-","-")
text = replace(text,"<","<")
text = replace(text,">",">")
tidyxml = text
End function

thedate = Month(CurrDate) & "/" & Day(CurrDate) & "/" & Year(CurrDate)
if Month(CurrDate) = 1 then
thepreviousdate = "12/" & Day(CurrDate) & "/" & (Year(CurrDate)-1)
else
thepreviousdate = (Month(CurrDate)-1) & "/" & Day(CurrDate) & "/" &
Year(CurrDate)
end if

newsSQL = "SELECT StoryID,Headline, Description, DateEntered FROM Stories "
& _
WHERE DateEntered BETWEEN " & _
"#" & thedate & " 23:59:59# And #" & thepreviousdate & " 0:0:1#;"
set conn = server.createobject("ADODB.Connection")
conn.Open connectionstring
set rsNews = conn.execute(newsSQL)

If Not rs.EOF Then

arrNews = rsNews.GetRows()
rsNews.close : set rsNews = nothing
conn.close : set conn = nothing

filename = "rss/rss.xml"
set fso = createobject("scripting.filesystemobject")
set xmlfile = fso.createtextfile(server.mappath(filename),true)

xmlfile.writeline("<?xml version=""1.0"" encoding=""iso-8859-1""?>")
xmlfile.writeline("<rss version=""2.0"">")
xmlfile.writeline("<channel>")
xmlfile.writeline("<title>Title</title>")
xmlfile.writeline("<link>Url</link>")
xmlfile.writeline("<description>Description</description>")
xmlfile.writeline("<language>en-gb</language>")

CurrDate = Now()
CurrHour = Hour(CurrDate)
if CurrHour < 10 then CurrHour = "0" & CurrHour
CurrMin = Minute(CurrDate)
if CurrMin < 10 then CurrMin = "0" & CurrMin
CurrSec = Second(CurrDate)
if CurrSec < 10 then CurrSec = "0" & CurrSec
CurrDateT = WeekdayName(Weekday(CurrDate), TRUE) & ", " & Day(CurrDate) & "
" & _
MonthName(Month(CurrDate), TRUE) & " " & Year(CurrDate) & " " & _
CurrHour & ":" & CurrMin & ":" & CurrSec & " GMT"

xmlfile.writeline("<lastBuildDate>" & CurrDateT & "</lastBuildDate>")
xmlfile.writeline("<copyright>Copyright: (C) " & Year(Now()) & "
CopyrightOwner</copyright>")

for i = 0 to ubound(arrNews,2)
PubDate = arrNews(3,i)
PubHour = Hour(PubDate)
if PubHour < 10 then PubHour = "0" & PubHour
PubMin = Minute(PubDate)
if PubMin < 10 then PubMin = "0" & PubMin
PubSec = Second(PubDate)
if PubSec < 10 then PubSec = "0" & PubSec

PubDateT = WeekdayName(Weekday(PubDate), TRUE) & ", " & Day(PubDate) & " " &
_
MonthName(Month(PubDate), TRUE) & " " & Year(PubDate) & " " & _
PubHour & ":" & PubMin & ":" & PubSec & " GMT"

xmlfile.writeline("<item>")
xmlfile.writeline("<title>" & tidyxml(arrNews(1,i)) & "</title>")
xmlfile.writeline("<description>" & tidyxml(arrNews(2,i)) &
"</description>")
xmlfile.writeline("<link>URL/story.asp?id=" & arrNews(0,i) & "</link>")
xmlfile.writeline("<guid isPermaLink=""false"">URL/story.asp?id=" &
arrNews(0,i) & "</guid>")
xmlfile.writeline("<pubDate>" & PubDateT & "</pubDate>")
xmlfile.writeline("</item>")
next
xmlfile.writeline("</channel>")
xmlfile.writeline("</rss>")
set fso = nothing

Else
rs.close
End If
%>

 

[Billy Barth]

Was used to pull all stories entered within the last 30 days.
Obviously you can alter the date range, regional settings (eg
language, date format, time) and links according to your needs. The
result validates against RSS 2.0.

<%
function tidyxml(text)
text = replace(text,Chr(180),"'")
text = replace(text,"&","&amp;")
text = replace(text,"'","'")
text = replace(text,"'","'")
text = replace(text,""","""")
text = replace(text,""","""")

Thanks!

 

[ave]

I would like to create a RSS feed from my Access Database.
I have an Access table for news which has the article title, date, and
story in it. What I would like to do is pull the data from there into an
xml file to create the RSS feed, so when users upload a news story it
automatically updates in the xml file. Can this be done with asp? and where
should I look for more information. Thanks, This is a great group.

I have created a class in ASP that enables you to easily create Atom
feeds:

http://www.codercow.com/AtomXML/

It is not a complete implementation of the Atom standard, since I
doubt most people find it relevant to include ie. 2 different sizes of
images etc. But it is easy to extend to suit your needs.

Most people just use it out of the box.

Others have posted the necessary code to retrieve and run through a
recordset, all you have to do is call the method AddEntry for each
record.

Best regards,

Anders Vind Ebbesen