Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Generating the Feed Header

The Atom feed header contains mostly static data that could be generated within the server-side script. However, if we've decided to generate as much XML content as possible within the SQL server, it makes sense to generate the feed header within the same environment (plus you could replace static data with values from a parameter table).

The feed header is generated with a simple SELECT statement:

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’,
 ’tag:informit.com,2008:feeds/products’ AS ’id’,
 ’alternate’ AS ’link/@rel’,
 ’http://www.nil.com/www/eng/web.nsf/html/productsOnly’ AS ’link/@href’,
FOR XML PATH (’feed’)

The SELECT statement contains a number of interesting solutions that are worth explaining in more details:

  • A nested SELECT statement is used to generate the column value for the updated column.
  • The feed header has two link elements: one to specify the feed address itself (the element with rel=self attribute), the other to specify an alternate representation of the feed (the web page with product news).
  • The two link elements are generated by specifying the same element name (link) twice and using an intervening element (id) to split the attributes between the two.
  • SQL tables have no globally unique IDs, so we’re using the tag URI scheme to specify the feed identifier.
  • The WITH XMLNAMESPACES option specifies the default XML namespace required by the Atom standard.

The feed header generated by the SELECT statement is displayed in the following printout. It's by itself a valid Atom feed (you can verify it with the Feed Validation Service provided by the World Wide Web Consortium) lacking the actual product entries.

<feed xmlns="http://www.w3.org/2005/Atom">
 <title>Product announcements</title>
 <updated>2008-01-14T08:06:21+01:00</updated>
 <author>
  <name>Product marketing</name>
 </author>
 <link rel="self" href="http://www.nil.com/feeds/products" />
 <id>http://www.nil.com/feeds/products</id>
 <link rel="alternate"
  href="http://www.nil.com/www/eng/web.nsf/html/productsOnly" />
</feed>

To insert the entry elements generated with the EntryFeed view into the Atom document, we need to use a few advanced FOR XML PATH option concepts:

  • Nested SQL queries can generate XML fragments that are inserted in the final XML document produced by a SELECT statement with the FOR XML PATH option.
  • To generate an XML fragment in the nested SQL query, you have to specify FOR XML PATH(’name’),TYPE option in the nested SELECT statement to convert the results into the xml datatype.
  • The nested XML fragment is inserted directly into the final XML document if you use asterix as its column name.

The final SELECT statement that generates the Atom feed is shown in the next printout (the nested SQL query is highlighted):

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’,
 ’tag:informit.com,2008:feeds/products’ AS ’id’,
 ’alternate’ AS ’link/@rel’,
 ’http://www.nil.com/www/eng/web.nsf/html/productsOnly’ 
  AS ’link/@href’,
 (SELECT TOP 10 * FROM EntryFeed ORDER BY Updated DESC 
   FOR XML PATH (’entry’),TYPE) AS ’*’
FOR XML PATH (’feed’)

The final result is an Atom-compliant XML document with feed header and individual product entries:

<feed xmlns="http://www.w3.org/2005/Atom">
 <title>Product announcements</title>
 <updated>2008-01-14T08:06:21+01:00</updated>
 <author>
  <name>Product marketing</name>
 </author>
 <link rel="self" href="http://www.nil.com/feeds/products" />
 <id>http://www.nil.com/feeds/products</id>
 <link rel="alternate" href="http://www.nil.com/www/eng/web.nsf/html/productsOnly" />
 <entry xmlns="http://www.w3.org/2005/Atom">
  <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>
 <entry xmlns="http://www.w3.org/2005/Atom">
  <link href="http://www.nil.com/ls/HUCS" />
  <title>Hosted Unified Communications System</title>
  <content type="html">This is a first level course covering the Cisco Hosted Unified Communications System or HUCS. </content>
  <updated>2008-01-11T08:41:58+01:00</updated>
  <id>urn:uuid:5C13EBED-F383-41CE-B99B-0BD7184D4AFE</id>
 </entry>  ... </feed>
  • + Share This
  • 🔖 Save To Your Account