Creating an RSS (or XML) file with ASP

Hi all,

I have a database of 1000 properties. I would like to create an ASP or possibly a VBS file which will execute daily at a certain time, delete the old XML file, create a new XML file and write the RSS information it gets from the database with a loop. How can I create file and write information? Any ideas or examples?

Thanks

Use the File System Object to create your XML file.

<%
Dim FilePath
Dim FileName
Dim oFSO
Dim oNewFile

FilePath ="The_location_on_the_server_where_you_are_going_to_save_the_file_goes_here"
FileName = "The_name_of_the_file_goes_here"

Set oFSO = Server.CreateObject("Scripting.FileSystemObject")
Set oNewFile = oFSO.CreateTextFile(FilePath & FileName)

oNewFile.WriteLine"<?xml version=""1.0"" ?>"
oNewFile.WriteLine"<rss version=""2.0"">"
oNewFile.WriteLine"<channel>"
sSQL = "SELECT link, description, guid FROM YourDB"
oRs.Open sSQL, YourConnection
Do Until oRs.Eof
oNewFile.WriteLine"<item>"
oNewFile.WriteLine"<link>" & oRs("link") & "</link>"
oNewFile.WriteLine"<description>" & oRs("description") & "</description>"
oNewFile.WriteLine"<pubDate>" & Now() & "</pubDate>"
oNewFile.WriteLine"<guid>" & oRs("guid") & "</guid>"
oNewFile.WriteLine"</item>"
Loop
oRs.MoveNext
oRs.Close
Set oRs = Nothing

oNewFile.WriteLine"</channel>"
oNewFile.WriteLine"</rss>"

oNewFile.Close
Set oFSO = Nothing
%>

Thanks Miranda... You are a star...

Do you also know how I can create the "Sat, 07 Sep 2002 09:42:31 GMT" format for pubDate element of RSS2.0?

Assuming you are using SQL Server 2000 you can get the date/time as the GMT time.
So now your SQL Query would be
sSQL = "SELECT link, description, GETUTCDATE() AS UTCTime, guid FROM YourDB"



You can then format the string by writing a function similar to this one to actually display the string

Public Function ConvertToRSSDate(UTCTime)
TheDate = WeekdayName(UTCTime,true) & ", " & Day(UTCTime) & " " & _
MonthName(DatepaPart("mm",UTCTime),true) & " " & Year(UTCTime) & " " & _
Hour(UTCTime) & ":" & Minute(UTCTime) & ":" & Second(UTCTime) & " GMT"

ConvertToRSSDate = TheDate
End Function


Final code might look something like this

Public Function ConvertToRSSDate(UTCTime)
TheDate = WeekdayName(UTCTime,true) & ", " & Day(UTCTime) & " " & _
MonthName(DatepaPart("mm",UTCTime),true) & " " & Year(UTCTime) & " " & _
Hour(UTCTime) & ":" & Minute(UTCTime) & ":" & Second(UTCTime) & " GMT"

ConvertToRSSDate = TheDate
End Function


Dim FilePath
Dim FileName
Dim oFSO
Dim oNewFile

FilePath ="The_location_on_the_server_where_you_are_going_to_save_the_file_goes_here"
FileName = "The_name_of_the_file_goes_here"

Set oFSO = Server.CreateObject("Scripting.FileSystemObject")
Set oNewFile = oFSO.CreateTextFile(FilePath & FileName)

oNewFile.WriteLine"<?xml version=""1.0"" ?>"
oNewFile.WriteLine"<rss version=""2.0"">"
oNewFile.WriteLine"<channel>"
sSQL = "SELECT link, description, GETUTCDATE() AS UTCTime, guid FROM YourDB"
oRs.Open sSQL, YourConnection
Do Until oRs.Eof
sDate = oRs("UTCTime")
oNewFile.WriteLine"<item>"
oNewFile.WriteLine"<link>" & oRs("link") & "</link>"
oNewFile.WriteLine"<description>" & oRs("description") & "</description>"
oNewFile.WriteLine"<pubDate>" & ConvertToRSSDate(sDate) & "</pubDate>"
oNewFile.WriteLine"<guid>" & oRs("guid") & "</guid>"
oNewFile.WriteLine"</item>"
Loop
oRs.MoveNext
oRs.Close
Set oRs = Nothing

oNewFile.WriteLine"</channel>"
oNewFile.WriteLine"</rss>"

oNewFile.Close
Set oFSO = Nothing