Creating an RSS (or XML) file with ASP

[telmessos]

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

[miranda]

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 
%>

[telmessos]

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

[telmessos]

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

[miranda]

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