- Anatomy of an Atom Feed
- Generating the Entry Elements
- Generating the Feed Header
- Adding the Glue
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Generating the Entry Elements
Atom feed is an XML document formatted according to RFC 4287. There are many ways to generate server-side XML documents from SQL data; I've described a few of them in the "Serve SQL Data in XML Format" Informit article. There are even open-source libraries that hide the (minimal) complexities of Atom—for example, the Atom.NET library described in the "Writing an Atom Feed" section of the Informit .NET Reference Guide.
If you're using the Microsoft's SQL server as the back-end database, you can leverage its inherent XML capabilities that I've briefly described in the "XML Handling in Microsoft SQL Server 2005" Informit article. The FOR XML option of the SELECT statement is ideal to generate XML data from SQL sets and the new PATH mode, introduced in SQL Server 2005 makes the XML generation extremely straightforward.
To start with, let's make simple entry elements with title, content, link, id, and updated children using the simplest rule of the PATH mode: If the column name is a word, it's used as the child element name.
SELECT ’http://www.nil.com/ls/’+[PartNo] AS ’link’, [ProductName] AS ’title’, [ProductDescription] AS ’content’, CONVERT(varchar(20),[Modified],127)+’+01:00’ as ’updated’, ’urn:uuid:’+CAST([UniqueID] AS varchar(max)) as ’id’ FROM Products FOR XML PATH(’title’)
The link element contains the URL that displays product information; you should insert your own web page that displays product descriptions in this element. The updated element is formatted according to ISO 8601 (CONVERT style 127), with the time zone added to satisfy the requirements of RFC 3339. As the SQL server does not store time zone information, the zone has to be coded manually (the +01:00 zone specifies Central European Time, Eastern Standard time is -05:00, Pacific Standard time is -08:00). The id element uses the unique ID field in the Universally Unique ID format (the urn:uuid: prefix).
The above SELECT statement generates the Atom entries in the following format:
<entry> <link>http://www.nil.com/ls/HUCS</link> <title>Hosted Unified Communications System</title> <content>This is a first level course ...</content> <updated>2008-01-11T08:41:58+01:00</updated> <id>urn:uuid:5C13EBED-F383-41CE-B99B-0BD7184D4AFE</id> </entry>
The results have two discrepancies from the Atom specifications:
- The IRI (in our case URL) in the link element should be specified in the href attribute, not in the element text.
- The content element should have the type="html" attribute to indicate that its text represents valid HTML.
We can satisfy both requirements using more complex PATH column names: if the column name in the SQL result is an XPATH expression, it's inserted in the correct child node. To set the href attribute of the link element, we set the column name to link/@href. Likewise, the content/@type column name sets the type attribute of the content child and the next column can specify its text:
SELECT ’http://www.nil.com/ls/’+[PartNo] AS ’link/@href’, [ProductName] AS ’title’, ’html’ as ’content/@type’, [ProductDescription] AS ’content’, CONVERT(varchar(20),[Modified],127)+’+01:00’ as ’updated’, ’urn:uuid:’+CAST([UniqueID] AS varchar(max)) as ’id’ FROM Products FOR XML PATH(’entry’)
The resulting entry (see the next listing) is perfect if the product data always contains the description. Otherwise, the content element is empty but still contains the type attribute if the ProductDescription column is NULL. The element’s text might also be misleading if the users enter "N/A" as the description text instead of leaving it empty.
<entry> <link href="http://www.nil.com/ls/HUCS" /> <title>Hosted Unified Communications System</title> <content type="html">This is ... </content> <updated>2008-01-11T08:41:58+01:00</updated> <id>urn:uuid:5C13EBED-F383-41CE-B99B-0BD7184D4AFE</id> </entry>
We thus have to replace the content as well as the content/@type columns with CASE expressions that result in NULL values if the ProductDescription column in the Products table contains NULL or N/A:
SELECT ’http://www.nil.com/ls/’+[PartNo] AS ’link/@href’, [ProductName] AS ’title’, CASE [ProductDescription] WHEN ’N/A’ THEN NULL WHEN NULL THEN NULL ELSE ’html’ END AS ’content/@type’, CASE [ProductDescription] WHEN ’N/A’ THEN NULL ELSE [ProductDescription] END AS ’content’, CONVERT(varchar(20),[Modified],127)+’+01:00’ as ’updated’, ’urn:uuid:’+CAST([UniqueID] AS varchar(max)) as ’id’ FROM Products FOR XML PATH(’entry’)
This SELECT statement returns Atom-compliant entries:
<entry> <link href="http://www.nil.com/ls/HUCS" /> <title>Hosted Unified Communications System</title> <content type="html">This is ... </content> <updated>2008-01-11T08:41:58+01:00</updated> <id>urn:uuid:5C13EBED-F383-41CE-B99B-0BD7184D4AFE</id> </entry> <entry> <link href="http://www.nil.com/ls/CCNA_Upd_WBT_SUB_3M" /> <title>CCNA Update</title> <updated>2008-01-14T08:06:21+01:00</updated> <id>urn:uuid:B7D1EFEB-FF9A-402C-BF5B-69D7C169727E</id> </entry>
To make subsequent processing simpler, we'll convert the SELECT statement into an SQL view:
CREATE VIEW EntryFeed AS SELECT ’http://www.nil.com/ls/’+[PartNo] AS ’link/@href’, [ProductName] AS ’title’, CASE [ProductDescription] WHEN ’N/A’ THEN NULL WHEN NULL THEN NULL ELSE ’html’ END AS ’content/@type’, CASE [ProductDescription] WHEN ’N/A’ THEN NULL ELSE [ProductDescription] END AS ’content’, CONVERT(varchar(20),[Modified],127)+’+01:00’ as ’updated’, ’urn:uuid:’+CAST([UniqueID] AS varchar(max)) as ’id’ FROM Products