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.
Sample Feed
This is the feed that we are trying to generate with FOR XML EXPLICIT.
A collection of short articles on SQL Server and XML
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />
FOR XML
A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML
href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
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
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,
title VARCHAR(100),
link VARCHAR(100),
published DATETIME,
updated DATETIME,
content VARCHAR(1000),
authorname VARCHAR(30),
authorurl VARCHAR(100))
GO
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(
title,
link,
published,
updated,
content,
authorname,
authorurl )
SELECT
'Sales Order Workshop',
'http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop',
'2005-11-24T00:25:00',
'2005-11-24T00:25:00',
'A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML',
'Jacob Sebastian',
'http://www.sqlserverandxml.com'
UNION ALL
SELECT
'FOR XML Workshop',
'http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop',
'2005-10-14T02:17:00',
'2005-10-14T02:17:00',
'A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.',
'Jacob Sebastian',
'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.
Step 1 - Create the root element
At the step, lets create root element of the feed. The query is quite simple.
SELECT
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
Step 2
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.
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'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'
FROM feed
FOR XML EXPLICIT
Step 3
Some of the children of "feed" do have their own children. One such example is the "title" element, which has an attribute: "type".
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'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',
NULL AS 'title!2!',
NULL AS 'title!2!type'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html'
FROM feed
FOR XML EXPLICIT
Step 4
Now let us generate the "subtitle" element.
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'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',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html'
FROM feed
FOR XML EXPLICIT
A collection of short articles on SQL Server and XML
Step 5
Generating the "link" element.
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'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',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link
FROM feed
FOR XML EXPLICIT
A collection of short articles on SQL Server and XML
href="http://www.sqlserverandxml.com/" />
Step 6
Since we need two link elements ("self" and "alternate"), we need to add one more UNION ALL.
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'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',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'self', 'application/atom+xml', id
FROM feed
FOR XML EXPLICIT
A collection of short articles on SQL Server and XML
href="http://www.sqlserverandxml.com/" />
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />
Step 7
Let us write the code for adding the "generator" element.
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'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',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href',
NULL AS 'generator!5',
NULL AS 'generator!5!uri',
NULL AS 'generator!5!version'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL,
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link,
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'self', 'application/atom+xml', id,
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
5 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
generator, link, '1.0'
FROM feed
FOR XML EXPLICIT
A collection of short articles on SQL Server and XML
href="http://www.sqlserverandxml.com/" />
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />
FOR XML
Step 8
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.
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'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',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href',
NULL AS 'generator!5',
NULL AS 'generator!5!uri',
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'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'self', 'application/atom+xml', id,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
5 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
generator, link, '1.0',
NULL, NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
6 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
title, link, CONVERT(nvarchar,published,127) + 'Z',
CONVERT(nvarchar,updated,127) + 'Z', content
FROM entry
FOR XML EXPLICIT
A collection of short articles on SQL Server and XML
href="http://www.sqlserverandxml.com/" />
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />
FOR XML
A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML
A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.
Step 9
Let us add the "link" element.
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'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',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href',
NULL AS 'generator!5',
NULL AS 'generator!5!uri',
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 AS 'link!7!',
NULL AS 'link!7!rel',
NULL AS 'link!7!type',
NULL AS 'link!7!href'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'self', 'application/atom+xml', id,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
5 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
generator, link, '1.0',
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
6 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
title, link, CONVERT(nvarchar,published,127) + 'Z',
CONVERT(nvarchar,updated,127) + 'Z', content,
NULL, NULL, NULL, NULL
FROM entry
UNION ALL
SELECT
7 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
link, 'alternate', 'text/html', link
FROM entry
FOR XML EXPLICIT
A collection of short articles on SQL Server and XML
href="http://www.sqlserverandxml.com/" />
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />
FOR XML
A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML
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/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
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".
Step 10
Let us add some sorting logic to make sure that the query returns rows in the correct order.
SELECT
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 (
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'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',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href',
NULL AS 'generator!5',
NULL AS 'generator!5!uri',
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 AS 'link!7!',
NULL AS 'link!7!rel',
NULL AS 'link!7!type',
NULL AS 'link!7!href',
0 AS EntryID, 0 AS Sort
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'self', 'application/atom+xml', id,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
5 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
generator, link, '1.0',
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
6 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
title, link, CONVERT(nvarchar,published,127) + 'Z',
CONVERT(nvarchar,updated,127) + 'Z', content,
NULL, NULL, NULL, NULL,
entryID, 1
FROM entry
UNION ALL
SELECT
7 AS Tag,
6 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
link, 'alternate', 'text/html', link,
entryID, 2
FROM entry
) A
ORDER BY EntryID, Sort
FOR XML EXPLICIT
A collection of short articles on SQL Server and XML
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />
FOR XML
A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML
href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
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
Step 11
We are almost done. This is the last step and we need to generate the "author" element.
SELECT
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],
[author!8!name!element],
[author!8!uri!element]
FROM (
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'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',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href',
NULL AS 'generator!5',
NULL AS 'generator!5!uri',
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 AS 'link!7!',
NULL AS 'link!7!rel',
NULL AS 'link!7!type',
NULL AS 'link!7!href',
NULL AS 'author!8!name!element',
NULL AS 'author!8!uri!element',
0 AS EntryID, 0 AS Sort
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'self', 'application/atom+xml', id,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
5 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
generator, link, '1.0',
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
6 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
title, link, CONVERT(nvarchar,published,127) + 'Z',
CONVERT(nvarchar,updated,127) + 'Z', content,
NULL, NULL, NULL, NULL,
NULL, NULL,
entryID, 1
FROM entry
UNION ALL
SELECT
7 AS Tag,
6 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
link, 'alternate', 'text/html', link,
NULL, NULL,
entryID, 2
FROM entry
UNION ALL
SELECT
8 AS Tag,
6 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
authorname, authorurl,
entryID, 3
FROM entry
) A
ORDER BY EntryID, Sort
FOR XML EXPLICIT
A collection of short articles on SQL Server and XML
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />
FOR XML
A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML
href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
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
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.
Conclusions
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.