Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Adding the Glue
Although the SELECT statement described in the previous section generates Atom-compliant content, we still need a server-side script that will send the content to the browser clients (it's not a good business practice to allow your Internet visitors to access your SQL server directly). To minimize the server-side scripting code (as well as isolate the Transact-SQL details from the ASP side of the solution), we'll pack the SELECT statement into a SQL Server stored procedure:
CREATE PROCEDURE GetProductFeed @MaxResults int = 20 AS BEGIN SET NOCOUNT ON; WITH XMLNAMESPACES(DEFAULT ’http://www.w3.org/2005/Atom’) SELECT ’Product announcements’ AS ’title’, (SELECT CONVERT(nvarchar(20),MAX(Products.MODIFIED),127)+ ’+01:00’ FROM Products) AS ’updated’, ’Product marketing’ AS ’author/name’, ’self’ AS ’link/@rel’, ’http://www.nil.com/feeds/products’ AS ’link/@href’, ’http://www.nil.com/feeds/products’ AS ’id’, ’alternate’ AS ’link/@rel’, ’http://www.nil.com/www/eng/web.nsf/html/productsOnly’ AS ’link/@href’, (SELECT TOP (@MaxResults) * FROM EntryFeed ORDER BY Updated DESC FOR XML PATH (’entry’),TYPE) AS ’*’ FOR XML PATH (’feed’),TYPE END
After the stored procedure is created, the server-side ASP script becomes exceedingly simple: It opens the database connection, executes the stored procedure, and tags the resulting string as UTF-encoded XML content:
Const FeedDSN = "Provider=SQLNCLI.1; User ID=InformIT; Initial Catalog=ProductFeed; Data Source=SQLServer; Password=changeme;"
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open FeedDSN
Set dbRS = dbConn.Execute("EXECUTE GetProductFeed 20")
Response.Clear
Response.ContentType = "text/xml"
Response.Charset = "utf-8"
Response.Codepage = 65001
Response.Write dbRS.Fields(0).Value
Response.End
You can download the complete ASP script from my web site or view its results on my development server. You can also download the underlying SQL Server database.