This article explains how to generate an RSS feed with iTunes extensions using TSQL.
What is an RSS Feed?
RSS is an XML document format that websites use to publish frequently updated information. An RSS reader application such as google reader or outlook RSS reader can query the RSS feed associated with a website and download newly updated content at specific intervals. If you are not familiar with RSS feeds, you can find more information here.
An icon similar to the one given here indicates that the website publishes RSS (sometimes RDF or ATOM) feeds and usually you can get feed URL by clicking on the icon. Some browsers like firefox displays a similar icon in the url address bar if it finds a feed on the websitie using its auto-discovery feature.
What is Podcasting?
In the recent years, RSS has become a very popular content model for publishing frequenlty changing information. This resulted in extending the RSS specification to accomodate additional information as part of the RSS feed. Podcasting is online audio content that is delivered via an RSS feed. An RSS reader can download them and play on a phone, ipod or computer. You can read about podcasting here.
What is iTunes?
iTunes is a media player application primarily used in Apple iPods. It allows a user to download, organize and play digital music and video files. You can read more about iTunes here.
What is RSS iTunes Extension?
iTunes extension is an extension to the RSS content specitication to include additional podcasting information to an RSS feed. An RSS reader or iTune aware application cann identify, download and play digital media files associated with an RSS feed item. iTunes extension defines additional elements that can be added to an RSS feed channel and item elements to publish the details of the associated digital media files.
Generating an RSS Feed using TSQL
I had written a series of articles at www.sqlservercentral.com that explain how to generate RSS feeds using TSQL. If you have not seen them, I would reccomend you take a look at them.
Generating RSS feeds with iTune extension
Generating an RSS feed with iTune extension involves generating the required iTune extension elements as part of the RSS channel and item element. Here is an example of the RSS feed that we are going to generate as part of this article.
<rss version="2.0" xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd">
<channel>
<title>XML Workshops</title>
<link>http://www.beyondrelational.com/xml.aspx</link>
<language>en-us</language>
<copyright>© beyondrelational.com</copyright>
<itunes:subtitle>XML Workshops</itunes:subtitle>
<itunes:author>Jacob Sebastian</itunes:author>
<itunes:summary>Explains SQL Server XML</itunes:summary>
<description>Explains SQL Server XML</description>
<itunes:owner>
<itunes:name>Jacob Sebastian</itunes:name>
<itunes:email>jacob@beyondrelational.com</itunes:email>
</itunes:owner>
<itunes:image href="http://beyondrelational.com/r.ashx?E" />
<itunes:category text="SQL Server" />
<item>
<title>XQuery Tutorials</title>
<itunes:author>Jacob Sebastian</itunes:author>
<itunes:subtitle>XQuery Tutorials</itunes:subtitle>
<itunes:summary>A collection of XQuery Tutorials</itunes:summary>
<guid>http://beyondrelational.com/xml/xquery.mp3</guid>
<pubDate>Thu, 04 Mar 2010 19:00:00 GMT</pubDate>
<itunes:duration>01:30</itunes:duration>
<itunes:keywords>SQL Server, XML, XQuery</itunes:keywords>
<enclosure url="http://beyondrelational.com/xml/xquery.mp3"
length="8727310"
type="audio/mpeg" />
</item>
<item>
<title>XSD Tutorials</title>
<itunes:author>Jacob Sebastian</itunes:author>
<itunes:subtitle>XSD Tutorials</itunes:subtitle>
<itunes:summary>A collection of XSD Tutorials</itunes:summary>
<guid>http://beyondrelational.com/xml/xsd.mp3</guid>
<pubDate>Thu, 04 Mar 2010 19:00:00 GMT</pubDate>
<itunes:duration>02:00</itunes:duration>
<itunes:keywords>SQL Server, XML, XSD</itunes:keywords>
<enclosure url="http://beyondrelational.com/xml/xsd.mp3"
length="5650889"
type="audio/mpeg" />
</item>
</channel>
</rss>
You need to add the following to a regular RSS feed to implement an iTune extension.
- Declaration of the iTunes namespace in the root element
- Additional itunes elements in the channel element
- Additional itunes elements in the item element
Getting Started
Let us get started with writing the TSQL code to generate the above XML document. As you would expect, we will use FOR XML to generate the XML document with the required strucutre. Before we proceed, let us create the tables to hold the sample data that we will use in this lab.
-- Create and populare "Channels" table
IF OBJECT_ID('Channels','U') IS NOT NULL BEGIN
DROP TABLE Channels
END
GO
CREATE TABLE Channels(
ChannelID INT IDENTITY PRIMARY KEY,
Title VARCHAR(50),
Link VARCHAR(100),
Summary VARCHAR(200),
Author VARCHAR(40),
AuthorEmail VARCHAR(50),
ImageURL VARCHAR(100),
Category VARCHAR(50) )
GO
INSERT INTO Channels (
Title,
Link,
Summary,
Author,
AuthorEmail,
ImageURL,
Category )
SELECT
'XML Workshops',
'http://www.beyondrelational.com/xml.aspx',
'Explains SQL Server XML',
'Jacob Sebastian',
'jacob@beyondrelational.com',
'http://beyondrelational.com/r.ashx?E',
'SQL Server'
-- Create and populare "Items" table
IF OBJECT_ID('Items','U') IS NOT NULL BEGIN
DROP TABLE Items
END
GO
CREATE TABLE Items (
ItemID INT IDENTITY PRIMARY KEY,
Title VARCHAR(50),
Summary VARCHAR(200),
Author VARCHAR(50),
EnclosureURL VARCHAR(100),
EnclosureLength INT,
EnclosureType VARCHAR(50),
PublicationDate DATETIME,
Duration VARCHAR (10),
Keywords VARCHAR(50)
)
GO
INSERT INTO Items (
Title, Summary, Author,
EnclosureURL, EnclosureLength, EnclosureType,
PublicationDate, Duration, Keywords )
SELECT
'XQuery Tutorials',
'A collection of XQuery Tutorials',
'Jacob Sebastian',
'http://beyondrelational.com/xml/xquery.mp3',
8727310, 'audio/mpeg',
'4 Mar 2010 19:00:00',
'01:30', 'SQL Server, XML, XQuery'
UNION ALL
SELECT
'XSD Tutorials',
'A collection of XSD Tutorials',
'Jacob Sebastian',
'http://beyondrelational.com/xml/xsd.mp3',
5650889, 'audio/mpeg',
'4 Mar 2010 19:00:00',
'02:00', 'SQL Server, XML, XSD'
Deciding the approach
As I mentioned earlier, we will use FOR XML to generate the RSS feed with iTunes extension. FOR XML can be used with AUTO, RAW, PATH or EXPLICIT and each of them provides different level of control over the structure of the XML output. EXPLICIT is the most complecated among all of them but gives you total control over the structure of the XML output. You can generate XML documents with almost any complex structure and shape using EXPLICIT.
Though FOR XML EXPLICIT is very powerful, the syntax is much complecated. SQL Server 2005 added PATH which is almost as powerful as EXPLICIT but has a much simpler syntax. Because PATH is very powerful and easy to use, I always recommend using PATH over EXPLICIT if the desired XML structre can be generated using PATH. However, there are times when you need EXPLICIT to get around a specific shaping problem.
The 'expected output' shown in this article can be generated using FOR XML PATH. Here is the TSQL code needed to generate the XML document having the same strucutre given above.
;WITH XMLNAMESPACES(
'http://www.itunes.com/dtds/podcast-1.0.dtd'
AS itunes
)
SELECT
'2.0' AS '@version',
(
SELECT
title as 'title',
link,
'en-us' as language,
'© beyondrelational.com' as copyright,
title as 'itunes:subtitle',
author as 'itunes:author',
summary as 'itunes:summary',
summary as 'description',
author as 'itunes:owner/itunes:name',
authoremail as 'itunes:owner/itunes:email',
ImageURL as 'itunes:image/@href',
category as 'itunes:category/@text',
(
SELECT
title,
author as 'itunes:author',
title as 'itunes:subtitle',
summary as 'itunes:summary',
EnclosureURL as 'guid',
LEFT(DATENAME(dw, PublicationDate),3)
+ ', ' +
STUFF(
CONVERT(nvarchar,PublicationDate,113),
21,
4,
' GMT')
as 'pubDate',
Duration as 'itunes:duration',
Keywords as 'itunes:keywords',
EnclosureURL as 'enclosure/@url',
EnclosureLength as 'enclosure/@length',
EnclosureType as 'enclosure/@type'
FROM items
FOR XML PATH('item'), TYPE
)
FROM Channels
FOR XML PATH('channel'), TYPE
)
FOR XML PATH('rss')
The above FOR XML PATH query produces an XML document with the same structure as the expected output given earlier. However, when looking at the output, you might notice the presence of repeated namespace declarations on all the top level nodes within the root element. In the original document, only the root element had namespace declaration. However, the output we just produced has namespace declarations on the 'channel' and 'item' elements as well. Here is the output produced by the above query (note that I have removed a lot of content for brevity and highlight the point I am trying to explain).
<rss xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd" version="2.0">
<channel xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd">
<title>XML Workshops</title>
<!-- removed for brevity -->
<itunes:owner>
<itunes:name>Jacob Sebastian</itunes:name>
<itunes:email>jacob@beyondrelational.com</itunes:email>
</itunes:owner>
<itunes:image href="http://beyondrelational.com/r.ashx?E" />
<itunes:category text="SQL Server" />
<item xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd">
<title>XQuery Tutorials</title>
<!-- removed for brevity -->
</item>
<!-- removed for brevity -->
</channel>
</rss>
The document we just generated is the same as what we saw in the 'expected output' section of this article. The only difference is that in our version, the 'channel' and 'item' elements too have namespace declarations as they exist in the root node. But that is not a problem. As per the XML specification, every child node inherits the namespace declarations from its parent. So whether you include the namespace declarations in the child node or not, does not make a difference. If you do not include a namespace declaration, it is inherited from the parent. So the meaning of the XML document does not change by repeating the namespace declarations in the child nodes.
Though those documents are the same as per the XML specification, there are some client applications that break if the child elements contain the namespace declarations of their parent element. In such a case, if you want to get rid of those namespace declarations, you will have to take a longer route using FOR XML EXPLICIT.
Here is the FOR XML EXPLICIT version of the above query which produces the EXACT result as given in the original sample.
SELECT
1 AS Tag,
NULL AS Parent,
'2.0' AS 'rss!1!version',
'http://www.itunes.com/dtds/podcast-1.0.dtd'
AS 'rss!1!xmlns:itunes',
'1' AS 'rss!1!sort!hide',
NULL AS 'channel!2!title!element',
NULL AS 'channel!2!link!element',
NULL AS 'channel!2!language!element',
NULL AS 'channel!2!copyright!element',
NULL AS 'channel!2!itunes:subtitle!element',
NULL AS 'channel!2!itunes:author!element',
NULL AS 'channel!2!itunes:summary!element',
NULL AS 'channel!2!description!element',
NULL AS 'itunes:owner!3!itunes:name!element',
NULL AS 'itunes:owner!3!itunes:email!element',
NULL AS 'itunes:image!4!href',
NULL AS 'itunes:category!5!text',
NULL AS 'item!6!title!element',
NULL AS 'item!6!itunes:author!element',
NULL AS 'item!6!itunes:subtitle!element',
NULL AS 'item!6!itunes:summary!element',
NULL AS 'item!6!guid!element',
NULL AS 'item!6!pubDate!element',
NULL AS 'item!6!itunes:duration!element',
NULL AS 'item!6!itunes:keywords!element',
NULL AS 'enclosure!7!url',
NULL AS 'enclosure!7!length',
NULL AS 'enclosure!7!type'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, '2', Title, Link,
'en-us', '© beyondrelational.com',
Title, Author, Summary, Summary,
NULL, NULL, ImageURL, Category,
NULL, NULL, NULL, NULL, NULL, NULL, null,
NULL, NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL, NULL, '3', NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, Author, AuthorEmail, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
4 AS Tag,
2 AS Parent,
NULL, NULL, '4', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, ImageURL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
5 AS Tag,
2 AS Parent,
NULL, NULL, '5', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, Category, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
6 AS Tag,
2 AS Parent,
NULL, NULL, '6' + STR(ItemID,4), NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, title,
Author, title, summary, EnclosureURL,
LEFT(DATENAME(dw, PublicationDate),3) + ', ' +
STUFF(CONVERT(nvarchar,PublicationDate,113),21,4,' GMT'),
Duration, Keywords, NULL, NULL, NULL
FROM Items
UNION ALL
SELECT
7 AS Tag,
6 AS Parent,
NULL, NULL, '6' + STR(ItemID,4) + '1', NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
EnclosureURL, EnclosureLength, EnclosureType
FROM Items
ORDER BY [rss!1!sort!hide]
FOR XML EXPLICIT
The above query produces an XML document with the same structure as the document given in the 'expected output' listing. As I mentioned earlier, FOR XML EXPLICIT is very powerful and allows you to generate XML documents with almost any shaping requirement. However, it has a much more complecated syntax and often people find it too difficult to work with FOR XML EXPLICIT.
I once wrote a series of FOR XML EXPLICIT tutorials. If you are not very comfortable writing FOR XML EXPLICIT queries, I would recommend you take a look at them.
Here is another version of the FOR XML EXPLICIT query which uses an embedded FOR XML PATH query within. This version has one UNION ALL query less than the previous version.
SELECT
1 AS Tag,
NULL AS Parent,
'2.0' AS 'rss!1!version',
'http://www.itunes.com/dtds/podcast-1.0.dtd'
AS 'rss!1!xmlns:itunes',
'1' AS 'rss!1!sort!hide',
NULL AS 'channel!2!title!element',
NULL AS 'channel!2!link!element',
NULL AS 'channel!2!language!element',
NULL AS 'channel!2!copyright!element',
NULL AS 'channel!2!itunes:subtitle!element',
NULL AS 'channel!2!itunes:author!element',
NULL AS 'channel!2!itunes:summary!element',
NULL AS 'channel!2!description!element',
NULL AS 'itunes:owner!3!itunes:name!element',
NULL AS 'itunes:owner!3!itunes:email!element',
NULL AS 'itunes:image!4!href',
NULL AS 'itunes:category!5!text',
NULL AS 'item!6!title!element',
NULL AS 'item!6!itunes:author!element',
NULL AS 'item!6!itunes:subtitle!element',
NULL AS 'item!6!itunes:summary!element',
NULL AS 'item!6!guid!element',
NULL AS 'item!6!pubDate!element',
NULL AS 'item!6!itunes:duration!element',
NULL AS 'item!6!itunes:keywords!element',
NULL AS 'item!6!!xml'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, '2', Title, Link,
'en-us', '© beyondrelational.com',
Title, Author, Summary, Summary,
NULL, NULL, ImageURL, Category,
NULL, NULL, NULL, NULL, NULL, NULL, null,
NULL, NULL
FROM Channels
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL, NULL, '3', NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, Author, AuthorEmail, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
4 AS Tag,
2 AS Parent,
NULL, NULL, '4', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, ImageURL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
5 AS Tag,
2 AS Parent,
NULL, NULL, '5', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, Category, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
6 AS Tag,
2 AS Parent,
NULL, NULL, '6' + STR(ItemID,4), NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, title,
Author, title, summary, EnclosureURL,
LEFT(DATENAME(dw, PublicationDate),3) + ', ' +
STUFF(CONVERT(nvarchar,PublicationDate,113),21,4,' GMT'),
Duration, Keywords,
(
SELECT
EnclosureURL AS '@url',
EnclosureLength AS '@length',
EnclosureType AS '@type'
FROM items i WHERE i.ItemID = itm.ItemID
FOR XML PATH('enclosure'), TYPE
)
FROM Items itm
ORDER BY [rss!1!sort!hide]
FOR XML EXPLICIT
Conslusions
The purpose of this article is to demonstrate how to generate an RSS feed with iTune extensions. The query given in this article does not generate all the elements in the RSS 2.0 specification. It does not geneate all the elements in the iTunes extension specification as well. However, this will help you to get started and add the additional elements and attributes to your RSS document following the same method demonstrated in this article.