The last few sessions of XML Workshop were focussing on generating RSS and ATOM feeds. Web Feeds (RSS or ATOM) are a must-have component of any modern web site. In the previous session, we have seen how to generate an ATOM 1.0 feed using FOR XML PATH. PATH is a new keyword introduced with SQL Server 2005 and is not available in SQL Server 2000. In this session, we will use FOR XML with EXPLICIT to generate an ATOM 1.0 feed. EXPLICIT is available in SQL Server 2000, 2005 and 2008.
You can find the previous sessions on RSS/ATOM feed generation here. All the previous sessions of XML Workshop are listed here. Several people asked me for an RSS feed for the XML Workshop series and I have created one here.
Let us move to the sample feed and sample data. Then we will start writing the FOR XML EXPLICIT code needed to generate the sample feed based on the sample data.
This is the feed that we are trying to generate with FOR XML EXPLICIT.
<feed xmlns="http://www.w3.org/2005/Atom">
<id>http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml</id>
<updated>2005-10-14T03:17:00Z</updated>
<title type="html">Welcome to XML Workshop</title>
<subtitle type="html">
A collection of short articles on SQL Server and XML
</subtitle>
<link rel="alternate" type="text/html" href="http://www.sqlserverandxml.com/" />
<link rel="self" type="application/atom+xml"
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />
<generator uri="http://www.sqlserverandxml.com/" version="1.0">
FOR XML
</generator>
<entry>
<title>Sales Order Workshop</title>
<id>http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop</id>
<published>2005-11-24T00:25:00Z</published>
<updated>2005-11-24T00:25:00Z</updated>
<content>
A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML
</content>
<link rel="alternate" type="text/html"
href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop</link>
<author>
<name>Jacob Sebastian</name>
<uri>http://www.sqlserverandxml.com</uri>
</author>
</entry>
<title>FOR XML Workshop</title>
<id>http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop</id>
<published>2005-10-14T02:17:00Z</published>
<updated>2005-10-14T02:17:00Z</updated>
A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.
href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop</link>
<uri>http://www.sqlserverandxml.com/</uri>
</feed>
Now let us have a look at the tables and the sample data. Here is the script to generate the sample tables.
-- table for the feed information
IF OBJECT_ID('feed') IS NOT NULL DROP TABLE feed
CREATE TABLE feed(
title VARCHAR(100),
subtitle VARCHAR(200),
id VARCHAR(100),
link VARCHAR(100),
generator VARCHAR(20),
updated DATETIME )
GO
IF OBJECT_ID('entry') IS NOT NULL DROP TABLE entry
-- table to store the entries
CREATE TABLE entry(
entryID INT IDENTITY,
published DATETIME,
updated DATETIME,
content VARCHAR(1000),
authorname VARCHAR(30),
authorurl VARCHAR(100))
And here is script to populate the tables with some sample data.
-- populate the 'feed' table
INSERT INTO feed (
title,
subtitle,
id,
link,
generator,
updated )
SELECT
'Welcome to XML Workshop',
'A collection of short articles on SQL Server and XML',
'http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml',
'http://www.sqlserverandxml.com/',
'FOR XML',
'2005-10-14T03:17:00'
-- populate the 'entry' table
INSERT INTO entry(
published,
updated,
content,
authorname,
authorurl )
'Sales Order Workshop',
'http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop',
'2005-11-24T00:25:00',
'A series of 4 articles which explain
using XML',
'Jacob Sebastian',
'http://www.sqlserverandxml.com'
UNION ALL
'FOR XML Workshop',
'http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop',
'2005-10-14T02:17:00',
'A collection of short articles that
XML.',
'http://www.sqlserverandxml.com/'
We have got the sample tables and sample data. Let us move ahead and write the code. We will develop the entire code in several small steps. We will start with a basic piece of code and will enhance it at every step. There will be some repetition, but I think it will help to understand FOR XML EXPLICIT behavior better.
At the step, lets create root element of the feed. The query is quite simple.
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns'
FROM feed
FOR XML EXPLICIT
<feed xmlns="http://www.w3.org/2005/Atom" />
In the previous step we created the root element "feed". At this step, we will generate the child elements of "feed". "feed" has two types of child elements. Some of the child elements have their own children and others do not have. In this step, lets generate "id" and "updated", the two child elements of "feed" that do not have other children.
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns',
id AS 'feed!1!id!element',
CONVERT(nvarchar,updated,127) + 'Z' AS 'feed!1!updated!element'
Some of the children of "feed" do have their own children. One such example is the "title" element, which has an attribute: "type".
CONVERT(nvarchar,updated,127) + 'Z' AS 'feed!1!updated!element',
NULL AS 'title!2!',
NULL AS 'title!2!type'
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html'
Now let us generate the "subtitle" element.
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type'
title, 'html',
NULL, NULL
3 AS Tag,
NULL, NULL,
subtitle, 'html'
Generating the "link" element.
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href'
NULL, NULL, NULL
subtitle, 'html',
4 AS Tag,
'alternate', 'text/html', link
href="http://www.sqlserverandxml.com/" />
Since we need two link elements ("self" and "alternate"), we need to add one more UNION ALL.
CONVERT(nvarchar,updated,127) + 'Z'
AS 'feed!1!updated!element',
'self', 'application/atom+xml', id
Let us write the code for adding the "generator" element.
NULL AS 'link!4!href',
NULL AS 'generator!5',
NULL AS 'generator!5!uri',
NULL AS 'generator!5!version'
NULL, NULL, NULL,
'alternate', 'text/html', link,
'self', 'application/atom+xml', id,
5 AS Tag,
generator, link, '1.0'
We are done with the "feed" element. Let us now start adding the "entries". Some of the children of "entry" have children and others do not. At this step, let us generate the elements, which do not have children.
NULL AS 'generator!5!version',
NULL AS 'entry!6!title!element',
NULL AS 'entry!6!id!element',
NULL AS 'entry!6!published!element',
NULL AS 'entry!6!updated!element',
NULL AS 'entry!6!content!element'
NULL, NULL, NULL, NULL, NULL
generator, link, '1.0',
6 AS Tag,
title, link, CONVERT(nvarchar,published,127) + 'Z',
CONVERT(nvarchar,updated,127) + 'Z', content
FROM entry
Let us add the "link" element.
NULL AS 'entry!6!content!element',
NULL AS 'link!7!',
NULL AS 'link!7!rel',
NULL AS 'link!7!type',
NULL AS 'link!7!href'
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
CONVERT(nvarchar,updated,127) + 'Z', content,
7 AS Tag,
link, 'alternate', 'text/html', link
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
</link>
http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop
Well, we landed with the sorting problem again. We saw this in the previous sessions where we discussed FOR XML EXPLICIT. When using FOR XML EXPLICIT, we need to make sure that the query returns rows in the same order as you need them in the XML document. You will see that the above XML is not correct, because the "link" element of both "entry" nodes are positioned at the bottom of the XML document. Each "link" element should be placed inside its own "entry" node. To achieve this, we need to add some kind of sorting logic to the query. We need to make sure that the row for the "link" element of the first "entry" should appear immediately after the row of the first "entry".
Let us add some sorting logic to make sure that the query returns rows in the correct order.
Tag,
Parent,
[feed!1!],
[feed!1!xmlns],
[feed!1!id!element],
[feed!1!updated!element],
[title!2!],
[title!2!type],
[subtitle!3],
[subtitle!3!type],
[link!4!rel],
[link!4!type],
[link!4!href],
[generator!5],
[generator!5!uri],
[generator!5!version],
[entry!6!title!element],
[entry!6!id!element],
[entry!6!published!element],
[entry!6!updated!element],
[entry!6!content!element],
[link!7!],
[link!7!rel],
[link!7!type],
[link!7!href]
FROM (
NULL AS 'link!7!href',
0 AS EntryID, 0 AS Sort
0, 0
entryID, 1
6 AS Parent,
link, 'alternate', 'text/html', link,
entryID, 2
) A
ORDER BY EntryID, Sort
We are almost done. This is the last step and we need to generate the "author" element.
[link!7!href],
[author!8!name!element],
[author!8!uri!element]
NULL AS 'author!8!name!element',
NULL AS 'author!8!uri!element',
8 AS Tag,
authorname, authorurl,
entryID, 3
Congratulations! We have the feed we wanted. You can validate the feed we just generated with FeedValidator.org or any of the validator of your choice. I have validated it only with FeedValidator.org. If your feed validator reports a problem with the above feed, please let me know.
We just saw another session on FOR XML EXPLICIT. Those of you who have good control over FOR XML EXPLICIT might find quite a lot of repetitive stuff here. FOR XML EXPLICIT seems to be very confusing to many people. The most complicated part is that people often land up with errors after writing a lengthy query. I am more in favor of taking "baby-steps" by using a step-by-step approach for writing FOR XML EXPLICIT queries. At each step we could add the code for a new element and test the results. If an error occurs, we could rollback to the previous step and quickly fix/spot the problem.
I have used this step-by-step approach to help many people who contacted me with strange errors/behaviors with lengthy FOR XML EXPLICIT queries. If you are a new-bie and trying to write a FOR XML EXPLICIT query, I would suggest using a step-by-step approach as given in this session.
To read the rest of this article, and access thousands of other articles, we ask you to register on the site and subscribe to our newsletters.
Subscribing to our newsletters gets you:
Steve Jones Editor, SQLServerCentral.com
