Generating Atom Feed from SQL Data

NOTE

You can download the sample SQL Server database from my web site.

Anatomy of an Atom Feed

An Atom feed has pretty simple structure well documented in RFC 4287. I would highly recommend that you read the RFC if you want to know the in-depth details of the Atom format. I'll just give you the basic information you need to build your first feed:

• Atom is an XML-based document format with XML namespace http://www.w3.org/2005/Atom.
• The root element of the Atom document is the feed element that contains a few feed-describing children and a number of entry elements describing individual items within the feed.
• The feed and the entry elements contain a number of child elements that describe them. The mandatory ones are id (uniquely identifying the feed or the element), title (name of the feed or the title of the entry), author, and updated elements.
• The feed element could contain category descriptions, icon pointers, logo, rights information (for example, copyright text), as well as any extension element.
• The entry element must contain a content, summary, or link child element. It could also contain list of categories, links to alternate representations of the entry (for example, HTML page), list of contributors, and a published date/time.

The feed and entry id is an Internationalized Resource Identifier (IRI). It could be a URL that displays the feed or the entry, or you could use any other registered URI scheme—for example, the Uniform Resource Names syntax to use Universally Unique Identifiers.

In our examples, we'll extend the database structure with a new uniqueidentifier column to provide universally unique identifiers for our products:

ALTER TABLE Products ADD UniqueID uniqueidentifier 
 NOT NULL DEFAULT NEWID()

The Atom standard also requires a timestamp for the feed as well as for each entry element to give the readers the capability to reliably identify changes in the feed content. The timestamps have to conform to the ISO 8601 date/time format (also specified in RFC 3339). To enable the timestamps in the Atom feed, we'll add a Modified field to the Products table. Its default value will be current date/time:

ALTER TABLE Products ADD Modified Datetime 
 NOT NULL DEFAULT GETDATE()

Furthermore, we should update the Modified field each time the product data is updated to ensure that the Atom readers will fetch the updated contents. The easiest way to do it is with an UPDATE trigger:

CREATE TRIGGER UpdateProducts ON Products AFTER UPDATE
 AS BEGIN
  UPDATE Products SET Modified = GETDATE()
   WHERE PartNo IN (SELECT PartNo FROM Inserted)
 END