XML Workshop 20 - Generating an RSS 2.0 Feed with TSQL(SQL server 2000)XML Workshop XX - Generating an RSS 2.0 Feed with TSQL(SQL server 2000)

Introduction
In XML Workshop XVIII, we have seen how to generate an RSS 2.0 feed from TSQL. The session explained the feed generation process step by step and used FOR XML PATH to generate a valid RSS 2.0 feed.

FOR XML PATH is a very powerful keyword that provides a great deal of control over the structure of the XML document being generated. We could generate very complex XML structures by using FOR XML with PATH. PATH is a new keyword introduced with SQL Server 2005 and hence it is not available in SQL Server 2000. The focus of this session is to write the TSQL code for SQL Server 2000 that generates a valid RSS 2.0 feed. Since PATH is not available in SQL Server 2000, we will use FOR XML with EXPLICIT to generate the feed. In the previous sessions of XML Workshop, we have had a good look into TSQL keyword FOR XML along with AUTO, RAW, PATH and EXPLICIT.

Sample Feed
For the purpose of this example, let us assume that we need to create an RSS 2.0 feed that contains all the articles in the XML Workshop series. To keep the examples simple, we will process only two records. Here is the output that we expect to generate by the end of this LAB.




http://www.sqlserverandxml.com/...central.html

A collection of short articles on SQL Server and XML

jacob@dotnetquest.com (Jacob Sebastian)
en-us
Jacob Sebastian. All rights reserved.
Wed, 12 Mar 2008 23:45:02 GMT
100

http://www.sqlserverandxml.com/image.jpg

http://www.sqlserverandxml.com/...central.html
144
22



http://www.sqlservercentral.com/...2982.asp

A short article that explains how to generate XML output
with TSQL keyword FOR XML

Wed, 12 Mar 2008 23:45:02 GMT
http://www.sqlservercentral.com/...2982.asp



http://www.sqlservercentral.com/...2996/

This article explains how to read values from an XML variable
using XQuery

Wed, 12 Mar 2008 23:45:02 GMT
http://www.sqlservercentral.com/...2996/



Sample Tables and Data
Let us create two tables to store the data needed for this LAB. We need one table to store the information about the RSS Channel and another table for storing the data of each RSS item. Here is the script for those tables.

IF OBJECT_ID('channel') IS NOT NULL DROP TABLE Channel
GO

CREATE TABLE channel(
Title VARCHAR(100),
Link VARCHAR(100),
Description VARCHAR(200),
WebMaster VARCHAR(50),
Language VARCHAR(20),
ImageUrl VARCHAR(100),
ImageTitle VARCHAR(100),
ImageLink VARCHAR(100),
ImageWidth SMALLINT,
ImageHeight SMALLINT,
CopyRight VARCHAR(100),
LastBuildDate DATETIME,
ttl SMALLINT )
GO

IF OBJECT_ID('Articles') IS NOT NULL DROP TABLE Articles
GO

CREATE TABLE Articles(
ArticleID INT IDENTITY(1,1),
Title VARCHAR(100),
Link VARCHAR(100),
Description VARCHAR(200),
Guid VARCHAR(100),
PubDate DATETIME )
GO
Here is the code to populate the tables with some sample data

INSERT INTO channel (
Title,
Link,
Description,
Webmaster,
Language,
ImageUrl,
ImageTitle,
ImageLink,
ImageWidth,
ImageHeight,
CopyRight,
LastBuildDate,
ttl)
SELECT
'Welcome to XML Workshop',
'http://www.sqlserverandxml.com/...central.html',
'A collection of short articles on SQL Server and XML',
'jacob@dotnetquest.com (Jacob Sebastian)',
'en-us',
'http://www.sqlserverandxml.com/image.jpg',
'Welcome to XML Workshop',
'http://www.sqlserverandxml.com/...central.html',
144,
22,
'Jacob Sebastian. All rights reserved.',
'2008-03-12 23:45:02',
100


INSERT INTO Articles (
Title,
Link,
Description,
Guid,
PubDate )
SELECT
'XML Workshop I - Generating XML with FOR XML',
'http://www.sqlservercentral.com/...2982.asp',
'A short article that explains how to generate XML output
with TSQL keyword FOR XML',
'http://www.sqlservercentral.com/...2982.asp',
'2008-03-12 23:45:02'
UNION ALL
SELECT
'XML Workshop II - Reading values from XML variables',
'http://www.sqlservercentral.com/...2996/',
'This article explains how to read values from an XML variable
using XQuery',
'http://www.sqlservercentral.com/...2996/',
'2008-03-12 23:45:02'
Generating the feed
Let us start writing the TSQL code to generate the feed. Let us break the task into different steps and attempt one step at a time.

Step 1
Let us generate the root element at this step. The root element of an RSS 2.0 feed is the rss element.

SELECT
1 AS Tag,
NULL AS Parent,
'2.0' AS 'rss!1!version'
FOR XML EXPLICIT

Step 2
Let us generate the channel element at this step. The channel element is little complicated because it has a number of child elements and some of the child elements have their children too. So at this step, let us just create a basic declaration of the channel element.

SELECT
1 AS Tag,
NULL AS Parent,
'2.0' AS 'rss!1!version',
NULL AS 'channel!2!title!element'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
Title
FROM channel

FOR XML EXPLICIT





Step 3
Let us enhance the code a little more so that it includes all the child elements of channel.

SELECT
1 AS Tag,
NULL AS Parent,
'2.0' AS 'rss!1!version',
NULL AS 'channel!2!title!element',
NULL AS 'channel!2!link!element',
NULL AS 'channel!2!description!element',
NULL AS 'channel!2!webMaster!element',
NULL AS 'channel!2!language!element',
NULL AS 'channel!2!copyright!element',
NULL AS 'channel!2!lastBuildDate!element',
NULL AS 'channel!2!ttl!element'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
Title ,
Link,
Description,
WebMaster,
Language,
CopyRight,
LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +
STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT'),
ttl
FROM channel

FOR XML EXPLICIT



http://www.sqlserverandxml.com/...central.html
A collection of short articles on SQL Server and XML
jacob@dotnetquest.com (Jacob Sebastian)
en-us
Jacob Sebastian. All rights reserved.
Wed, 12 Mar 2008 23:45:02 GMT
100


Step 4
The structure of channel element is little complicated. One of its child element, image has other child elements too. This leads us to generate an additional level in the XML hierarchy. Lets us write the code to generate this structure.

SELECT
1 AS Tag,
NULL AS Parent,
'2.0' AS 'rss!1!version',
NULL AS 'channel!2!title!element',
NULL AS 'channel!2!link!element',
NULL AS 'channel!2!description!element',
NULL AS 'channel!2!webMaster!element',
NULL AS 'channel!2!language!element',
NULL AS 'channel!2!copyright!element',
NULL AS 'channel!2!lastBuildDate!element',
NULL AS 'channel!2!ttl!element',
NULL AS 'image!3!url!element',
NULL AS 'image!3!title!element',
NULL AS 'image!3!link!element',
NULL AS 'image!3!width!element',
NULL AS 'image!3!height!element'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
Title ,
Link,
Description,
WebMaster,
Language,
CopyRight,
LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +
STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT'),
ttl,
NULL, NULL, NULL, NULL, NULL
FROM channel
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
ImageUrl,
ImageTitle,
ImageLink,
ImageWidth,
ImageHeight
FROM channel
FOR XML EXPLICIT



http://www.sqlserverandxml.com/...central.html

A collection of short articles on SQL Server and XML

jacob@dotnetquest.com (Jacob Sebastian)
en-us
Jacob Sebastian. All rights reserved.
Wed, 12 Mar 2008 23:45:02 GMT
100

http://www.sqlserverandxml.com/image.jpg

http://www.sqlserverandxml.com/...central.html
144
22



Step 5
We are done with the channel element. Let us move to the item element. Let us do it in two steps. First let us see if we can correctly generate the item elements with just the title information.

SELECT
1 AS Tag,
NULL AS Parent,
'2.0' AS 'rss!1!version',
NULL AS 'channel!2!title!element',
NULL AS 'channel!2!link!element',
NULL AS 'channel!2!description!element',
NULL AS 'channel!2!webMaster!element',
NULL AS 'channel!2!language!element',
NULL AS 'channel!2!copyright!element',
NULL AS 'channel!2!lastBuildDate!element',
NULL AS 'channel!2!ttl!element',
NULL AS 'image!3!url!element',
NULL AS 'image!3!title!element',
NULL AS 'image!3!link!element',
NULL AS 'image!3!width!element',
NULL AS 'image!3!height!element',
NULL AS 'item!4!title!element'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
Title ,
Link,
Description,
WebMaster,
Language,
CopyRight,
LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +
STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT'),
ttl,
NULL, NULL, NULL, NULL, NULL,
NULL
FROM channel
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
ImageUrl,
ImageTitle,
ImageLink,
ImageWidth,
ImageHeight,
NULL
FROM channel
UNION ALL
SELECT
4 AS Tag,
2 AS Parent,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
title
FROM Articles
FOR XML EXPLICIT



http://www.sqlserverandxml.com/...central.html
A collection of short articles on SQL Server and XML
jacob@dotnetquest.com (Jacob Sebastian)
en-us
Jacob Sebastian. All rights reserved.
Wed, 12 Mar 2008 23:45:02 GMT
100

http://www.sqlserverandxml.com/image.jpg

http://www.sqlserverandxml.com/...central.html
144
22









Step 6
It looks like we are getting there. Let us write the query to generate the other elements too.

SELECT
1 AS Tag,
NULL AS Parent,
'2.0' AS 'rss!1!version',
NULL AS 'channel!2!title!element',
NULL AS 'channel!2!link!element',
NULL AS 'channel!2!description!element',
NULL AS 'channel!2!webMaster!element',
NULL AS 'channel!2!language!element',
NULL AS 'channel!2!copyright!element',
NULL AS 'channel!2!lastBuildDate!element',
NULL AS 'channel!2!ttl!element',
NULL AS 'image!3!url!element',
NULL AS 'image!3!title!element',
NULL AS 'image!3!link!element',
NULL AS 'image!3!width!element',
NULL AS 'image!3!height!element',
NULL AS 'item!4!title!element',
NULL AS 'item!4!link!element',
NULL AS 'item!4!description!element',
NULL AS 'item!4!guid!element',
NULL AS 'item!4!pubDate!element'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
Title ,
Link,
Description,
WebMaster,
Language,
CopyRight,
LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +
STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT'),
ttl,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM channel
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
ImageUrl,
ImageTitle,
ImageLink,
ImageWidth,
ImageHeight,
NULL, NULL, NULL, NULL, NULL
FROM channel
UNION ALL
SELECT
4 AS Tag,
2 AS Parent,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
title,
Link,
Description,
Guid,
LEFT(DATENAME(dw, PubDate),3) + ', ' +
STUFF(CONVERT(nvarchar,PubDate,113),21,4,' GMT')
FROM Articles
FOR XML EXPLICIT



http://www.sqlserverandxml.com/...central.html

A collection of short articles on SQL Server and XML

jacob@dotnetquest.com (Jacob Sebastian)
en-us
Jacob Sebastian. All rights reserved.
Wed, 12 Mar 2008 23:45:02 GMT
100

http://www.sqlserverandxml.com/image.jpg

http://www.sqlserverandxml.com/...central.html
144
22



http://www.sqlservercentral.com/...2982.asp

A short article that explains how to generate XML output
with TSQL keyword FOR XML

http://www.sqlservercentral.com/...2982.asp
Wed, 12 Mar 2008 23:45:02 GMT



http://www.sqlservercentral.com/...2996/

This article explains how to read values from an XML
variable using XQuery

http://www.sqlservercentral.com/...2996/
Wed, 12 Mar 2008 23:45:02 GMT



Step 7
Well, we are almost done. The only remaining task is to add the attribute isPermalink with each item element. Let us try to add that.

SELECT
1 AS Tag,
NULL AS Parent,
'2.0' AS 'rss!1!version',
NULL AS 'channel!2!title!element',
NULL AS 'channel!2!link!element',
NULL AS 'channel!2!description!element',
NULL AS 'channel!2!webMaster!element',
NULL AS 'channel!2!language!element',
NULL AS 'channel!2!copyright!element',
NULL AS 'channel!2!lastBuildDate!element',
NULL AS 'channel!2!ttl!element',
NULL AS 'image!3!url!element',
NULL AS 'image!3!title!element',
NULL AS 'image!3!link!element',
NULL AS 'image!3!width!element',
NULL AS 'image!3!height!element',
NULL AS 'item!4!title!element',
NULL AS 'item!4!link!element',
NULL AS 'item!4!description!element',
NULL AS 'item!4!pubDate!element',
NULL AS 'guid!5!isPermaLink',
NULL AS 'guid!5!!element'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
Title ,
Link,
Description,
WebMaster,
Language,
CopyRight,
LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +
STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT'),
ttl,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL
FROM channel
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
ImageUrl,
ImageTitle,
ImageLink,
ImageWidth,
ImageHeight,
NULL, NULL, NULL, NULL,
NULL, NULL
FROM channel
UNION ALL
SELECT
4 AS Tag,
2 AS Parent,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
title,
Link,
Description,
LEFT(DATENAME(dw, PubDate),3) + ', ' +
STUFF(CONVERT(nvarchar,PubDate,113),21,4,' GMT'),
NULL, NULL
FROM Articles
UNION ALL
SELECT
5 AS Tag,
4 AS Parent,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
'true',
guid
FROM Articles
FOR XML EXPLICIT



http://www.sqlserverandxml.com/...central.html

A collection of short articles on SQL Server and XML

jacob@dotnetquest.com (Jacob Sebastian)
en-us
Jacob Sebastian. All rights reserved.
Wed, 12 Mar 2008 23:45:02 GMT
100

http://www.sqlserverandxml.com/image.jpg

http://www.sqlserverandxml.com/...central.html
144
22



http://www.sqlservercentral.com/...2982.asp

A short article that explains how to generate XML output
with TSQL keyword FOR XML

Wed, 12 Mar 2008 23:45:02 GMT



http://www.sqlservercentral.com/...2996/

This article explains how to read values from an XML variable
using XQuery

Wed, 12 Mar 2008 23:45:02 GMT
http://www.sqlservercentral.com/...2982.asp
http://www.sqlservercentral.com/...2996/



Step 8
We have a problem here. The isPermalink attribute should be generated for each item element. At present they appear with the last element only. The problem is with the physical order of the query result. We need to make sure that the isPermalink row appears along with the rows of each item. We need to add some sort of ordering logic to get this done. Here is the updated version of the query.

SELECT
Tag,
Parent,
[rss!1!version],
[channel!2!title!element],
[channel!2!link!element],
[channel!2!description!element],
[channel!2!webMaster!element],
[channel!2!language!element],
[channel!2!copyright!element],
[channel!2!lastBuildDate!element],
[channel!2!ttl!element],
[image!3!url!element],
[image!3!title!element],
[image!3!link!element],
[image!3!width!element],
[image!3!height!element],
[item!4!title!element],
[item!4!link!element],
[item!4!description!element],
[item!4!pubDate!element],
[guid!5!isPermaLink],
[guid!5!!element]
FROM (
SELECT
1 AS Tag,
NULL AS Parent,
'2.0' AS 'rss!1!version',
NULL AS 'channel!2!title!element',
NULL AS 'channel!2!link!element',
NULL AS 'channel!2!description!element',
NULL AS 'channel!2!webMaster!element',
NULL AS 'channel!2!language!element',
NULL AS 'channel!2!copyright!element',
NULL AS 'channel!2!lastBuildDate!element',
NULL AS 'channel!2!ttl!element',
NULL AS 'image!3!url!element',
NULL AS 'image!3!title!element',
NULL AS 'image!3!link!element',
NULL AS 'image!3!width!element',
NULL AS 'image!3!height!element',
NULL AS 'item!4!title!element',
NULL AS 'item!4!link!element',
NULL AS 'item!4!description!element',
NULL AS 'item!4!pubDate!element',
NULL AS 'guid!5!isPermaLink',
NULL AS 'guid!5!!element',
CAST(1 AS VARBINARY(4)) AS Sort
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
Title ,
Link,
Description,
WebMaster,
Language,
CopyRight,
LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +
STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT'),
ttl,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL,
CAST(1 AS VARBINARY(4)) + CAST(2 AS VARBINARY(4))
FROM channel
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
ImageUrl,
ImageTitle,
ImageLink,
ImageWidth,
ImageHeight,
NULL, NULL, NULL, NULL,
NULL, NULL,
CAST(1 AS VARBINARY(4)) + CAST(2 AS VARBINARY(4))
+ CAST(3 AS VARBINARY(4))
FROM channel
UNION ALL
SELECT
4 AS Tag,
2 AS Parent,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
title,
Link,
Description,
LEFT(DATENAME(dw, PubDate),3) + ', ' +
STUFF(CONVERT(nvarchar,PubDate,113),21,4,' GMT'),
NULL, NULL,
CAST(1 AS VARBINARY(4)) + CAST(2 AS VARBINARY(4))
+ CAST(3 AS VARBINARY(4))
+ CAST(ArticleID AS VARBINARY(4))
FROM Articles
UNION ALL
SELECT
5 AS Tag,
4 AS Parent,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
'true',
guid,
CAST(1 AS VARBINARY(4)) + CAST(2 AS VARBINARY(4))
+ CAST(3 AS VARBINARY(4))
+ CAST(ArticleID AS VARBINARY(4))
+ CAST(ArticleID AS VARBINARY(4))
FROM Articles
) a
ORDER BY SORT
FOR XML EXPLICIT



http://www.sqlserverandxml.com/...central.html

A collection of short articles on SQL Server and XML

jacob@dotnetquest.com (Jacob Sebastian)
en-us
Jacob Sebastian. All rights reserved.
Wed, 12 Mar 2008 23:45:02 GMT
100

http://www.sqlserverandxml.com/image.jpg

http://www.sqlserverandxml.com/...central.html
144
22



http://www.sqlservercentral.com/...2982.asp

A short article that explains how to generate XML output
with TSQL keyword FOR XML

Wed, 12 Mar 2008 23:45:02 GMT

http://www.sqlservercentral.com/...2982.asp




http://www.sqlservercentral.com/...2996/

This article explains how to read values from an XML variable
using XQuery

Wed, 12 Mar 2008 23:45:02 GMT

http://www.sqlservercentral.com/...2996/




Conclusions
This is yet another session that demonstrates an XML shaping example. We have seen different XML shaping requirements and their implementation in the previous sessions of the XML Workshop. This session explains the basics of generating an RSS 2.0 feed using TSQL keyword FOR XML EXPLICIT

XML Workshop 19 - Generating an ATOM 1.0 Feed

Introduction
In the previous article, I had presented an example that generates an RSS 2.0 feed using TSQL. In this session, we will generate an ATOM 1.0 feed. ATOM is another popular feed format and you can find the specification here.

Most of the times, web applications generate feeds at the application layer. The feed generator would execute a query/stored-procedure, fetch the data and generate the XML document using custom application code or a third party library like ATOM.NET. The purpose of this session is to look deeper into the XML capabilities of SQL Server 2005 and see if it could generate an ATOM 1.0 with a FOR XML operation. The examples and code given in this article are purely for the purpose of explaining how XML data can be generated in TSQL.

In the previous article, I had explained how to validate a web feed with an online feed validator. Though there are several online feed validators available, we will use FeedValidator.org to validate the feeds that we generate in this session. You could try the feeds with other feed validators and get back to me if you find an issue.

Sample Feed
Here is the output that we expect to generate by the end of this LAB.




A collection of short articles on
SQL Server and XML


http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml

type="text/html" href="http://www.sqlserverandxml.com/" />
type="application/atom+xml"
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />

FOR XML

2005-10-14T03:17:00Z


href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop

http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
2005-11-24T00:25:00Z
2005-11-24T00:25:00Z

A series of 4 articles that explain
how to pass variable number of parameters to a stored procedure
using XML


Jacob Sebastian
http://www.sqlserverandxml.com




href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop

http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop
2005-10-14T02:17:00Z
2005-10-14T02:17:00Z

A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.


Jacob Sebastian
http://www.sqlserverandxml.com/



Before we proceed further, we need to make sure that the XML output that we intend to generate is a valid ATOM 1.0 document. You could test this by using feedvalidator.org. Open a browser window and navigate to feedvalidator.org. Enter the url of the above sample ATOM 1.0 feed and click on the "validate" button.

Sample Tables and Data
Let us create two tables to store the data needed for this LAB. We need one table to store the information about the feed element and another table for storing the data of each entry in the feed. Here is the script of those tables.

-- table for the feed information
CREATE TABLE feed(
title VARCHAR(100),
subtitle VARCHAR(200),
id VARCHAR(100),
link VARCHAR(100),
generator VARCHAR(20),
updated DATETIME )
GO

-- table to store the entries
CREATE TABLE entry(
title VARCHAR(100),
link VARCHAR(100),
published DATETIME,
updated DATETIME,
content VARCHAR(1000),
authorname VARCHAR(30),
authorurl VARCHAR(100))
GO
Here is the code 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/'
Generating the feed
Let us start with the entry element, which is pretty much easy. Here is the code that generates the entry elements.

SELECT
title,
'alternate' AS 'link/@rel',
'text/html' AS 'link/@type',
link AS 'link/@href',
link,
link AS 'id',
published,
updated,
content,
authorname AS 'author/name',
authorurl AS 'author/uri'
FROM entry FOR XML PATH('entry'), TYPE
The above code produces the following XML document.



href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop


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




href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop

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/


Though the XML looks good, there is a problem. The format of the date values (published and updated) are not correct. ATOM 1.0 requires that the date value should be in RFC 3339 date format. So we need to format the date value to a valid RFC 3339 date value. Here is the modified version of the code.

SELECT
title,
'alternate' AS 'link/@rel',
'text/html' AS 'link/@type',
link AS 'link/@href',
link,
link AS 'id',
CONVERT(nvarchar,published,127) + 'Z' AS published,
CONVERT(nvarchar,updated,127) + 'Z' AS updated,
content,
authorname AS 'author/name',
authorurl AS 'author/uri'
FROM entry FOR XML PATH('entry'), TYPE
Here is the corrected XML.



href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop

http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
2005-11-24T00:25:00Z
2005-11-24T00:25:00Z

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




href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop

http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop
2005-10-14T02:17:00Z
2005-10-14T02:17:00Z

A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.


Jacob Sebastian
http://www.sqlserverandxml.com/


Now, let us write the query to generate the "feed" element.

SELECT
'html' AS 'title/@type',
title,
'html' AS 'subtitle/@type',
subtitle,
id,
(
SELECT
'alternate' AS 'link/@rel',
'text/html' AS 'link/@type',
link AS 'link/@href'
FROM feed FOR XML PATH(''), TYPE
),
(
SELECT
'self' AS 'link/@rel',
'application/atom+xml' AS 'link/@type',
id AS 'link/@href'
FROM feed FOR XML PATH(''), TYPE
),
link AS 'generator/@uri',
'1.0' AS 'generator/@version',
generator,
CONVERT(VARCHAR(20),updated,127) + 'Z' AS updated
FROM feed
FOR XML PATH('feed'),TYPE




A collection of short articles on SQL Server and XML


http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml

href="http://www.sqlserverandxml.com/" />
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />

FOR XML

2005-10-14T03:17:00Z

Unlike RSS 2.0 feeds, ATOM 1.0 should have a namespace declaration. We could add a namespace declaration by adding WITH XMLNAMESPACES clause. Here is how we will achieve this.

;WITH XMLNAMESPACES(
DEFAULT 'http://www.w3.org/2005/Atom'
)
SELECT
'html' AS 'title/@type',
title,
'html' AS 'subtitle/@type',
subtitle,
id,
(
SELECT
'alternate' AS 'link/@rel',
'text/html' AS 'link/@type',
link AS 'link/@href'
FROM feed FOR XML PATH(''), TYPE
),
(
SELECT
'self' AS 'link/@rel',
'application/atom+xml' AS 'link/@type',
id AS 'link/@href'
FROM feed FOR XML PATH(''), TYPE
),
link AS 'generator/@uri',
'1.0' AS 'generator/@version',
generator,
CONVERT(VARCHAR(20),updated,127) + 'Z' AS updated
FROM feed
FOR XML PATH('feed'),TYPE



A collection of short articles on SQL Server and XML


http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml

rel="alternate" type="text/html"
href="http://www.sqlserverandxml.com/" />
rel="self" type="application/atom+xml"
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />

FOR XML

2005-10-14T03:17:00Z

We have seen how to generate the feed element as well as entry elements. Now it is time to merge the queries and produce the final result.

;WITH XMLNAMESPACES(
DEFAULT 'http://www.w3.org/2005/Atom'
)
SELECT
'html' AS 'title/@type',
title,
'html' AS 'subtitle/@type',
subtitle,
id,
(
SELECT
'alternate' AS 'link/@rel',
'text/html' AS 'link/@type',
link AS 'link/@href'
FROM feed FOR XML PATH(''), TYPE
),
(
SELECT
'self' AS 'link/@rel',
'application/atom+xml' AS 'link/@type',
id AS 'link/@href'
FROM feed FOR XML PATH(''), TYPE
),
link AS 'generator/@uri',
'1.0' AS 'generator/@version',
generator,
CONVERT(VARCHAR(20),updated,127) + 'Z' AS updated,
(
SELECT
title,
'alternate' AS 'link/@rel',
'text/html' AS 'link/@type',
link AS 'link/@href',
link,
link AS 'id',
CONVERT(nvarchar,published,127) + 'Z' AS published,
CONVERT(nvarchar,updated,127) + 'Z' AS updated,
content,
authorname AS 'author/name',
authorurl AS 'author/uri'
FROM entry FOR XML PATH('entry'), TYPE
)
FROM feed
FOR XML PATH('feed'),TYPE



A collection of short articles on SQL Server and XML


http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml

rel="alternate" type="text/html"
href="http://www.sqlserverandxml.com/" />
rel="self" type="application/atom+xml"
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />

FOR XML

2005-10-14T03:17:00Z


href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop

http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
2005-11-24T00:25:00Z
2005-11-24T00:25:00Z

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




href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop

http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop
2005-10-14T02:17:00Z
2005-10-14T02:17:00Z

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 a valid ATOM 1.0 feed at this point. This feed validates successfully with a few feed validators that I tried. However, you might notice something strange with this feed. Usually you will see ATOM 1.0 feeds having namespace declaration only in the feed element. In our case, we have namespace declaration added to the link and entry elements too. This is the way 'WITH XMLNAMESPACES' works. There is no direct way to get rid of this.

A feed validator does not seem to complain about this. But I am not sure if all feed readers will accept this. Technically this should not be a problem. If you want to get rid of this additional namespace declarations on the child elements, you might need to do some string manipulation like in the example given below.

SELECT CAST( '' +
CAST(
(SELECT
'html' AS 'title/@type',
title,
'html' AS 'subtitle/@type',
subtitle,
id,
(
SELECT
'alternate' AS 'link/@rel',
'text/html' AS 'link/@type',
link AS 'link/@href'
FROM feed FOR XML PATH(''), TYPE
),
(
SELECT
'self' AS 'link/@rel',
'application/atom+xml' AS 'link/@type',
id AS 'link/@href'
FROM feed FOR XML PATH(''), TYPE
),
link AS 'generator/@uri',
'1.0' AS 'generator/@version',
generator,
CONVERT(VARCHAR(20),updated,127) + 'Z' AS updated,
(
SELECT
title,
'alternate' AS 'link/@rel',
'text/html' AS 'link/@type',
link AS 'link/@href',
link,
link AS 'id',
CONVERT(nvarchar,published,127) + 'Z' AS published,
CONVERT(nvarchar,updated,127) + 'Z' AS updated,
content,
authorname AS 'author/name',
authorurl AS 'author/uri'
FROM entry FOR XML PATH('entry'), TYPE
)
FROM feed
FOR XML PATH(''),TYPE)
AS NVARCHAR(MAX)) + '' AS XML)



A collection of short articles on SQL Server and XML

http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
href="http://www.sqlserverandxml.com/" />
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />

FOR XML

2005-10-14T03:17:00Z


href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop

http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
2005-11-24T00:25:00Z
2005-11-24T00:25:00Z

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




href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop

http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop
2005-10-14T02:17:00Z
2005-10-14T02:17:00Z

A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.


Jacob Sebastian
http://www.sqlserverandxml.com/



You could validate this version of the output with feedvalidator.org to make sure that we have a valid ATOM 1.0 feed.

Conclusions
This is yet another session that demonstrates an XML shaping example. We have seen different XML shaping requirements and their implementation in the previous sessions of the XML Workshop. This session explains the basics of generating an ATOM 1.0 feed using TSQL keyword FOR XML PATH. In the next session, I will present some code that explains how to achieve this in SQL Server 2000.

XML Workshop 18 - Generating an RSS 2.0 Feed with TSQL

Introduction
In the previous sessions of XML Workshop, we have seen several XML processing examples. Some of the examples demonstrated how to generate XML output in a specific structure. In this session of XML Workshop, let us see how to generate an RSS feed using TSQL. Most web sites today adhere to the Web 2.0 standards and RSS/ATOM feeds are an integral part of them. If you are not familiar with RSS feeds, you can find a basic introduction here. RSS has several versions and the one that is widely used today is version 2.0. You can find the documentation of RSS 2.0 here.

Most of the times, web applications generate feeds at the application layer. The feed generator would execute a query/stored-procedure, fetch the data and generate the XML document using custom application code or a third party library like RSS.NET. The purpose of this session is to look deeper into the XML capabilities of SQL Server 2005 and see if it could generate an RSS 2.0 with a FOR XML operation. The examples and code given in this article is purely for the purpose of explaining how XML data can be generated in TSQL.

As mentioned earlier, an RSS 2.0 feed should follow a certain structure and rules. There are certain mandatory elements and attributes. Certain values like pubDate should be in a specific format. Most applications that read RSS feeds (RSS Readers) validate the feed against the rules given in the RSS specification and will reject the feed if it does not follow the rules defined in the specification. An online feed validator like FeedValidator.org can be used to validate the RSS feed to make sure that it follows all the rules defined by the RSS specification. In this session, we will generate an RSS 2.0 feed and validate it with FeedValidator.org.

Sample Feed
For the purpose of this example, let us assume that we need to create an RSS 2.0 feed that contains all the articles in the XML Workshop series. To keep the examples simple, we will process only two records. Here is the output that we expect to generate by the end of this LAB.





http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html
A collection of short articles on SQL Server and XML
jacob@dotnetquest.com (Jacob Sebastian)
en-us

http://www.sqlserverandxml.com/image.jpg

http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html
144
22

Jacob Sebastian. All rights reserved.
Wed, 12 Mar 2008 23:45:02 GMT
100


http://www.sqlservercentral.com/columnists/jSebastian/2982.asp
A short article that explains how to generate XML output with TSQL keyword FOR XML
http://www.sqlservercentral.com/columnists/jSebastian/2982.asp
Wed, 12 Mar 2008 23:45:02 GMT



http://www.sqlservercentral.com/articles/Miscellaneous/2996/
This article explains how to read values from an XML variable using XQuery
http://www.sqlservercentral.com/articles/Miscellaneous/2996/
Wed, 12 Mar 2008 23:45:02 GMT



Before we proceed further, we need to make sure that the XML output that we intend to generate is a valid RSS 2.0 document. You could test this by using feedvalidator.org. Open a browser window and navigate to feedvalidator.org. Enter the url of the above sample RSS 2.0 feed and click on the "validate" button.

Sample Tables and Data
Let us create two tables to store the data needed for this LAB. We need one table to store the information about the RSS Channel and another table for storing the data of each RSS item. Here is the script for those tables.

CREATE TABLE channel(
Title VARCHAR(100),
Link VARCHAR(100),
Description VARCHAR(200),
WebMaster VARCHAR(50),
Language VARCHAR(20),
ImageUrl VARCHAR(100),
ImageTitle VARCHAR(100),
ImageLink VARCHAR(100),
ImageWidth SMALLINT,
ImageHeight SMALLINT,
CopyRight VARCHAR(100),
LastBuildDate DATETIME,
ttl SMALLINT )
GO

CREATE TABLE Articles(
Title VARCHAR(100),
Link VARCHAR(100),
Description VARCHAR(200),
Guid VARCHAR(100),
PubDate DATETIME )
GO
Here is the code to populate the tables with some sample data

INSERT INTO channel (
Title,
Link,
Description,
Webmaster,
Language,
ImageUrl,
ImageTitle,
ImageLink,
ImageWidth,
ImageHeight,
CopyRight,
LastBuildDate,
ttl)
SELECT
'Welcome to XML Workshop',
'http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html',
'A collection of short articles on SQL Server and XML',
'jacob@dotnetquest.com (Jacob Sebastian)',
'en-us',
'http://www.sqlserverandxml.com/image.jpg',
'Welcome to XML Workshop',
'http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html',
144,
22,
'Jacob Sebastian. All rights reserved.',
'2008-03-12 23:45:02',
100


INSERT INTO Articles (
Title,
Link,
Description,
Guid,
PubDate )
SELECT
'XML Workshop I - Generating XML with FOR XML',
'http://www.sqlservercentral.com/columnists/jSebastian/2982.asp',
'A short article that explains how to generate XML output with TSQL keyword FOR XML',
'http://www.sqlservercentral.com/columnists/jSebastian/2982.asp',
'2008-03-12 23:45:02'
UNION ALL
SELECT
'XML Workshop II - Reading values from XML variables',
'http://www.sqlservercentral.com/articles/Miscellaneous/2996/',
'This article explains how to read values from an XML variable using XQuery',
'http://www.sqlservercentral.com/articles/Miscellaneous/2996/',
'2008-03-12 23:45:02'
Generating the feed
Let us start with the item element, which is pretty much easy. Here is the code that generates the item elements.

SELECT
Title AS title,
Link AS link,
Description AS description,
'true' AS 'guid/@isPermaLink',
Guid AS guid,
PubDate AS pubDate
FROM Articles FOR XML PATH('item'), TYPE
The above code produces the following XML document.



http://www.sqlservercentral.com/columnists/jSebastian/2982.asp
A short article that explains how to generate XML output with TSQL keyword FOR XML
http://www.sqlservercentral.com/columnists/jSebastian/2982.asp
2008-03-12T23:45:02



http://www.sqlservercentral.com/articles/Miscellaneous/2996/
This article explains how to read values from an XML variable using XQuery
http://www.sqlservercentral.com/articles/Miscellaneous/2996/
2008-03-12T23:45:02

Though the XML looks good, there is a problem. The format of the date value (pubDate) is not correct. RSS 2.0 requires that the date value should be in RFC 822 date format. So we need to format the date value to a valid RFC 822 date value. Here is the modified version of the code.

SELECT
Title AS title,
Link AS link,
Description AS description,
'true' AS 'guid/@isPermaLink',
Guid AS guid,
LEFT(DATENAME(dw, PubDate),3) + ', ' +
STUFF(CONVERT(nvarchar,PubDate,113),21,4,' GMT')
AS pubDate
FROM Articles FOR XML PATH('item'), TYPE
Here is the corrected XML.



http://www.sqlservercentral.com/columnists/jSebastian/2982.asp
A short article that explains how to generate XML output with TSQL keyword FOR XML
http://www.sqlservercentral.com/columnists/jSebastian/2982.asp
Wed, 12 Mar 2008 23:45:02 GMT



http://www.sqlservercentral.com/articles/Miscellaneous/2996/
This article explains how to read values from an XML variable using XQuery
http://www.sqlservercentral.com/articles/Miscellaneous/2996/
Wed, 12 Mar 2008 23:45:02 GMT

Now, let us write the query to generate the "channel" element.

SELECT
Title AS title,
Link AS link,
Description AS description,
Webmaster AS webMaster,
Language AS language,
ImageUrl AS 'image/url',
ImageTitle AS 'image/title',
ImageLink AS 'image/link',
ImageWidth AS 'image/width',
ImageHeight AS 'image/height',
CopyRight AS copyright,
LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +
STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT')
AS lastBuildDate,
Ttl AS ttl,
(
SELECT
Title AS title,
Link AS link,
Description AS description,
'true' AS 'guid/@isPermaLink',
Guid AS guid,
LEFT(DATENAME(dw, PubDate),3) + ', ' +
STUFF(CONVERT(nvarchar,PubDate,113),21,4,' GMT')
AS pubDate
FROM Articles FOR XML PATH('item'), TYPE
)
FROM channel
FOR XML PATH('channel'), TYPE



http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html
A collection of short articles on SQL Server and XML
jacob@dotnetquest.com (Jacob Sebastian)
en-us

http://www.sqlserverandxml.com/image.jpg

http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html
144
22

Jacob Sebastian. All rights reserved.
Wed, 12 Mar 2008 23:45:02 GMT
100


http://www.sqlservercentral.com/columnists/jSebastian/2982.asp
A short article that explains how to generate XML output with TSQL keyword FOR XML
http://www.sqlservercentral.com/columnists/jSebastian/2982.asp
Wed, 12 Mar 2008 23:45:02 GMT



http://www.sqlservercentral.com/articles/Miscellaneous/2996/
This article explains how to read values from an XML variable using XQuery
http://www.sqlservercentral.com/articles/Miscellaneous/2996/
Wed, 12 Mar 2008 23:45:02 GMT


The next step is to add the root element and generate the xml header. here is the final version of the code.

SELECT
'' +
( SELECT
'2.0' AS '@version',
(
SELECT
Title AS title,
Link AS link,
Description AS description,
Webmaster AS webMaster,
Language AS language,
ImageUrl AS 'image/url',
ImageTitle AS 'image/title',
ImageLink AS 'image/link',
ImageWidth AS 'image/width',
ImageHeight AS 'image/height',
CopyRight AS copyright,
LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +
STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT')
AS lastBuildDate,
Ttl AS ttl,
(
SELECT
Title AS title,
Link AS link,
Description AS description,
'true' AS 'guid/@isPermaLink',
Guid AS guid,
LEFT(DATENAME(dw, PubDate),3) + ', ' +
STUFF(CONVERT(nvarchar,PubDate,113),21,4,' GMT')
AS pubDate
FROM Articles FOR XML PATH('item'), TYPE
)
FROM channel
FOR XML PATH('channel'), TYPE
)
FOR XML PATH('rss') )





http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html
A collection of short articles on SQL Server and XML
jacob@dotnetquest.com (Jacob Sebastian)
en-us

http://www.sqlserverandxml.com/image.jpg

http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html
144
22

Jacob Sebastian. All rights reserved.
Wed, 12 Mar 2008 23:45:02 GMT
100


http://www.sqlservercentral.com/columnists/jSebastian/2982.asp
A short article that explains how to generate XML output with TSQL keyword FOR XML
http://www.sqlservercentral.com/columnists/jSebastian/2982.asp
Wed, 12 Mar 2008 23:45:02 GMT



http://www.sqlservercentral.com/articles/Miscellaneous/2996/
This article explains how to read values from an XML variable using XQuery
http://www.sqlservercentral.com/articles/Miscellaneous/2996/
Wed, 12 Mar 2008 23:45:02 GMT



Conclusions
This is yet another session that demonstrates an XML shaping example. We have seen different XML shaping requirements and their implementation in the previous sessions of the XML Workshop. This session explains the basics of generating an RSS 2.0 feed using TSQL keyword FOR XML PATH

XML Workshop 17 - Writing a LOOP to process XML elements in TSQL

Introduction
In the previous sessions of XML Workshop, we have seen several XML processing examples. Some of the examples demonstrated how to generate XML output in a specific structure. Other few examples demonstrated how to read values from XML columns and XML variables.

While working with XML data, it may be common that you will come across situations where you need to iterate over an XML document. Most of the times we can avoid a LOOP by applying some sort of set-based logic and write a single query that does a batch processing. However, there may be cases when we cannot do a batch process. Assume that you need to run a loop over all the nodes of an XML document and execute a stored procedure for each node in the XML document.

In this session, I will show one of the several ways to iterate over an XML document. As I used to mention, there are always more than one way to do a certain programming task. The approach presented here is one of the several possible options that can be used to achieve the given results.

The following pseudo code shows the basic flow of the iteration.

"i" = 1
"cnt" = total number of nodes
while "i" <= "cnt" begin
take the node at position "i"
process the node
increment "i"
end
Let us see how to translate the above pseudo code to TSQL code.

Sample XML Document
Let us work use the following XML document as the sample data for this session. The below XML document has 5 "Employee" elements. Let us write the code that iterates over the "Employee" elements and prints the "Name" of each employee.



Jacob
IT


Steve
IT


Bob
IT


Joe
IT


Louis
IT


Counting Nodes
Before we run the loop, we need to know how many "Employee" elements exist in the XML document. Once we know the total count of "Employee" nodes, we can run a loop from 1 to @count and at each iteration we can process a single XML node.

To count the number of nodes, let us use the XQuery function "count()". Here is the code that returns the count of nodes.

DECLARE @x XML
SET @x =
'

Jacob
IT


Steve
IT


Bob
IT


Joe
IT


Louis
IT

'

SELECT
@x.query('
{ count(/Employees/Employee) }
') AS Count

/*
output:

Count
-----------------
5
*/
We need to retrieve the value "5" from the XQuery result. The following query retrieves the value "5" form the XQuery result.

DECLARE @x XML
SET @x =
'

Jacob
IT


Steve
IT


Bob
IT


Joe
IT


Louis
IT

'

SELECT
@x.query('
{count(/Employees/Employee)}
'
).value('count[1]','int') AS cnt

/*
output:

cnt
-----------
5
*/
Accessing the XML node at a given position
The next task is to see how to access a NODE at a given position. The XQuery function "position()" can be used to access the XML node at a given position.

The following code shows an example of accessing the 3rd "Employee" node using "position()" method.

DECLARE @x XML
SET @x =
'

Jacob
IT


Steve
IT


Bob
IT


Joe
IT


Louis
IT

'

SELECT
x.value('Name[1]', 'VARCHAR(20)') AS Name
FROM @x.nodes('/Employees/Employee[position()=3]') e(x)

/*
output:

Name
--------------------
Bob
*/
Using a variable to specify the position
We just saw how to access an XML node at a given position. Now let us see how to access a node at the position specified by a variable.

The following example shows how to use a variable in the XPath expression.

DECLARE @x XML
SET @x =
'

Jacob
IT


Steve
IT


Bob
IT


Joe
IT


Louis
IT

'

DECLARE @i TINYINT
SELECT @i = 3

SELECT
x.value('Name[1]', 'VARCHAR(20)') AS Name
FROM
@x.nodes('/Employees/Employee[position()=sql:variable("@i")]') e(x)

/*
output:

Name
--------------------
Bob
*/
Writing the LOOP
It is time to look at the final source code. Here is the loop which runs over all the "Employee" elements and prints the "Name" of each employee.

DECLARE @x XML
SET @x =
'

Jacob
IT


Steve
IT


Bob
IT


Joe
IT


Louis
IT

'

-- Total count of Nodes
DECLARE @max INT, @i INT
SELECT
@max = @x.query('
{ count(/Employees/Employee) }
').value('e[1]','int')

-- Set counter variable to 1
SET @i = 1

-- variable to store employee name
DECLARE @EmpName VARCHAR(10)

-- loop starts
WHILE @i <= @max BEGIN
-- select "Name" to the variable
SELECT
@EmpName = x.value('Name[1]', 'VARCHAR(20)')
FROM
@x.nodes('/Employees/Employee[position()=sql:variable("@i")]')
e(x)

-- print the name
PRINT @EmpName

-- increment counter
SET @i = @i + 1
END
Conclusions
We have learned how to use XQuery functions "count()" and "position()". Then we have seen how to access an XML node at the position specified by a variable. Finally, we have seen a loop which runs over all the nodes of an XML document and prints the name of an element.

XML Workshop 16 - Shaping the XML results

Introduction
In the previous sessions of XML Workshop, we have seen several examples of generating XML results using FOR XML along with RAW, AUTO, PATH and EXPLICIT modes. In the previous sessions, we have learned how to control the structure of XML being generated. This session presents one more example which shows shaping the query results to a certain pre-defined XML structure.

Tables and Data
Let us have a look at the tables and data needed for this example. Here is the script to generate the tables and insert the data needed for this session.

CREATE TABLE Departments (DeptID INT, DeptName VARCHAR(20))
GO
INSERT INTO Departments (DeptID, DeptName)
SELECT 1, 'Software' UNION ALL
SELECT 2, 'Administration'

CREATE TABLE Employees(EmpID INT, EmpName VARCHAR(20), DeptID INT)
GO
INSERT INTO Employees (EmpID, EmpName, DeptID)
SELECT 1, 'Jacob', 1 UNION ALL
SELECT 2, 'Steve', 1 UNION ALL
SELECT 3, 'Bob', 2 UNION ALL
SELECT 4, 'Tom', 2
Our task is to generate the following XML from the above tables/data.















Generating the XML
The XML structure is a little more complex than we might think at first glance. The problem is the "Employees" element right after each department. If it were not there, it would have been easy with FOR XML AUTO as given below.

SELECT
Department.DeptID AS DepartmentID,
Department.DeptName AS DepartmentName,
Employee.EmpID AS EmployeeID,
Employee.EmpName AS EmployeeName
FROM Departments Department
INNER JOIN Employees Employee ON Department.DeptID = Employee.DeptID
FOR XML AUTO, ROOT('Departments')
This will produce the following output:











We could see that this is not the XML result that we needed. We need to put the employee records inside a separate element. The new PATH clause added by SQL Server 2005 is very powerful and can be used for a variety of XML shaping requirements. Let us try to use FOR XML PATH to get the XML structure that we need.

SELECT
d.DeptID AS '@DepartmentID',
d.DeptName AS '@DepartmentName',
(
SELECT
e.EmpID AS '@EmployeeID',
e.EmpName AS '@EmployeeName'
FROM Employees e WHERE e.DeptID = d.DeptID
FOR XML PATH('Employee'), TYPE
) AS Employees
FROM Departments d FOR XML PATH('Department'), ROOT('Departments')
The outer query generates the elements. The sub query generates the children of each Department and returns them as an XML node. The TYPE clause is used to return values as XML data type. Here is the result generated by the above query.















We could also use FOR XML EXPLICIT to generate the above XML, but it needs much more code than what we did in FOR XML PATH. FOR XML PATH can do most of the formatting requirements previously available only with EXPLICIT. Here is the FOR XML EXPLICIT version of the above code.

;WITH CTE AS (
SELECT
1 AS Tag,
NULL AS Parent,
DeptID AS 'Department!1!DepartmentID',
DeptName AS 'Department!1!DepartmentName',
NULL AS 'Employees!2!',
NULL AS 'Employee!3!EmployeeID',
NULL AS 'Employee!3!EmployeeName',
DeptID * 100 AS Sort
FROM Departments
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL, NULL, DeptID * 100 + 1
FROM Departments
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL, NULL, NULL,
EmpID, EmpName, DeptID * 100 + 1 + EmpID
FROM Employees
)
SELECT
Tag,
Parent,
[Department!1!DepartmentID],
[Department!1!DepartmentName],
[Employees!2!],
[Employee!3!EmployeeID],
[Employee!3!EmployeeName]
FROM cte
ORDER BY sort
FOR XML EXPLICIT, ROOT('Departments')
The "Sort" column is used to position records in the correct location. We need to put the employees of each departments right under their own tags and hence a custom Sort Order is generated. FOR XML EXPLICIT will write data to the output stream in the same order as the query returns. Hence we need to ensure that the data is returned in the correct order. Here is the result of the above query.















Conclusions
This session presented another XML formatting requirement and explained how to achieve it by using FOR XML PATH and FOR XML EXPLICIT. I guess some of you out there will come up with other ways of generating the above XML structure and will share your ideas in the discussion forum.

XML Workshop 15 - Accessing FOR XML results with ADO.NET

Introduction
In a few of the early sessions of the XML Workshop, we have seen how to generate XML results from TSQL. We have examined the FOR XML clause and have seen the usages of RAW, AUTO, PATH and EXPLICIT.

XML Workshop 1 - Explains FOR XML with AUTO and RAW

XML Workshop 3 - Explains FOR XML with PATH

XML Workshop 4 - Explains FOR XML with EXPLICIT



Accessing results of FOR XML from a .NET application
Most of the times the results of FOR XML queries are expected to be consumed by client applications. In this session, let us see how to access the results of FOR XML queries from VB.NET and C#.NET applications using ADO.NET.

Sample Table and Stored Procedure
Let us create a sample table and populate it with some data. Let us then create a stored procedure which generates an XML document using FOR XML.

-- Let us create a new database
CREATE DATABASE XmlTest
GO

-- Let us now create a table for our example
USE XmlTest
GO

CREATE TABLE Employee (EmpID INT IDENTITY(1,1), EmpName VARCHAR(50) )
GO

-- let us insert some data
INSERT INTO Employee (EmpName)
SELECT 'Jacob' UNION ALL
SELECT 'Michael' UNION ALL
SELECT 'Richard'
Let us now create a stored procedure which generates the XML document that we need.

CREATE PROCEDURE GetEmployeeData
AS

SELECT * FROM Employee
FOR XML AUTO, ROOT('Employees')
Here is the result of the stored procedure.






VB.NET Sample Code
Let us create a VB.NET console application which executes the above stored procedure and fetches the XML document. Here is the code which does that.

' references
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Text
Module Module1

Sub Main()
'let us make a connection first
Dim str As String
str = "Data Source=TOSHIBA-USER\SQL2005;Initial Catalog=XmlTest;"
str = str + "Persist Security Info=True;User ID=sa;Password=sa2005"
Dim cn As New SqlConnection(str)
cn.Open()

'Let us make a command
Dim cmd As New SqlCommand()
cmd.Connection = cn
cmd.CommandText = "GetEmployeeData"
cmd.CommandType = CommandType.StoredProcedure

'What we need next is an XMLReader and call
'ExecuteXMLReader method of SqlCommand.
Dim r As XmlReader
r = cmd.ExecuteXmlReader()

'Read the data from XMLReader and Load into
'the String Builder
Dim xmlData As New StringBuilder
Do While r.Read()
xmlData.Append(r.ReadOuterXml())
Loop

'Print the output
Console.WriteLine(xmlData.ToString)

'Close the Reader and DB Connection
r.Close()
cn.Close()

End Sub

End Module
C#.NET Sample Code
Now, let us write the C#.NET version of the above code.

using System;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Text;

namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{

//let us make a connection first
String str;
str = "Data Source=TOSHIBA-USER\\SQL2005;Initial Catalog=XmlTest;";
str = str + "Persist Security Info=True;User ID=sa;Password=sa2005";
SqlConnection cn = new SqlConnection(str);
cn.Open();

//Let us make a command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "GetEmployeeData";
cmd.CommandType = CommandType.StoredProcedure;

//What we need next is an XMLReader and call
//ExecuteXMLReader method of SqlCommand.
XmlReader r;
r = cmd.ExecuteXmlReader();

//Read the data from XMLReader and Load into
//the String Builder
StringBuilder xmlData = new StringBuilder();
while (r.Read())
{
xmlData.Append(r.ReadOuterXml());
}

//Print the output
Console.WriteLine(xmlData.ToString());

//Close the Reader and DB Connection
r.Close();
cn.Close();

}
}
}
Conclusions
This article presents an example of accessing FOR XML results from a .NET application. I guess there must be other ways of doing this too. This sample code is created for the purpose of demonstrating the basic usage. The sample applications are tested with the sample data and found to be working correctly. However, please note that the code is not highly optimized. You might need slightly different code for a production level application. For example, you might need to dispose() the database connection after reading the information. I leave that to the .NET developer in you

XML Workshop 14 - Generating an XML Tree

Introduction
I guess some of you might have come across the need for a hierarchical data structure in the day-to-day programming life. The most common task is the creation of a menu. Many applications store the menu data in a table with parent-child relationships duly applied. At run time, the menu data is read from the table and depending upon the user rights, filtered and the menu is created. To create a menu, we need a tree-like structure. We could do this easily with recursive calls to the data source (Local Data Set or from the Database Server).

There are many other situations where we need to work with a tree-structured data. But we usually get data in the shape of relational tables (Data Sets and Data Tables) and we might often need to do recursive calls to format the data to the shape that we need.

Since XML is good at managing and storing hierarchical data, at times it might make things easier if we can work with data in XML format. Many of the User Interface Controls available today (in different application development platforms) support data binding to an XML data source. In those cases, you might need to create an XML document from the data stored in one or more relational tables. So the question now is, how to generate an XML tree from relational data.

Generating an XML tree
If you need a tree structure with unlimited levels, a recursive function might be one choice. There is a an interesting white paper available at SQL Server Developer Center at MSDN web site. I found it to be pretty good. Towards the end of the document, it shows an approach which generates an XML tree by using a recursive function. I am sure that you will appreciate it.

Recently, in one of the MSDN forums, some one posted a question asking for an alternate way. He did not want to use a recursive function. He wanted to see if there is a way to generate an XML tree with a TSQL Query.

Here is the source table.

ID Parent Name
1 NULL car
2 1 engine
3 1 body
4 3 door
5 3 fender
6 4 window
7 2 piston
Here is the required result.

1
2
3
4
5
6
7
8
9
10
11

FOR XML EXPLICIT
We have seen how to use FOR XML EXPLICIT in XML WORKSHOP IV. I have also written a bit about EXPLICIT in my blog too. EXPLICIT gives good control over the structure of the XML being generated. The tough part is that EXPLICIT expects the data in a specific structure. We need to generate the data in the required structure and pass it to the XML processing engine. In this session, we will generate an XML tree using EXPLICIT.

Writing the code
Let us start writing the code. Let us create a sample table and populate it. Here is the code to create the table and populate it.

CREATE TABLE PARTS(
id int,
parent int,
name nvarchar(500))
GO
INSERT INTO PARTS
SELECT 1, NULL, N'car'
UNION
SELECT 2, 1, N'engine'
UNION
SELECT 3, 1, N'body'
UNION
SELECT 4, 3, N'door'
UNION
SELECT 5, 3, N'fender'
UNION
SELECT 6, 4, N'window'
UNION
SELECT 7, 2, N'piston'
Note: The above code is taken from the white paper mentioned earlier.

Let us now look at what kind of an input result set needs to be passed to FOR XML EXPLICIT. To generate the kind of XML structure that we need, we need to have input in the following structure.

Tag Parent Part!1!id Part!1!name Part!2!id Part!2!name Part!3!id Part!3!name Part!4!id Part!4!name
1 NULL 1 car NULL NULL NULL NULL NULL NULL
2 1 NULL NULL 2 engine NULL NULL NULL NULL
3 2 NULL NULL NULL NULL 7 piston NULL NULL
2 1 NULL NULL 3 body NULL NULL NULL NULL
3 2 NULL NULL NULL NULL 4 door NULL NULL
4 3 NULL NULL NULL NULL NULL NULL 6 window
3 2 NULL NULL NULL NULL 5 fender NULL NULL
Once we have the results in the above structure, the rest of the work will be done by the EXPLICIT operator. Let us now look at the query that will generate the result structure that we need. The parts table has a hierarchical relationship set between the records using the ID and Parent . We need to generate the results in such a way that the Tag column should contain the level or depth of the current node. To calculate the depth of each record, we need to use some sort of recursion. Since we do not want to use a recursive function, we will use a CTE. By using a CTE, we can write a recursive query in SQL Server 2005.

;WITH Parts1
AS
(
SELECT
0 AS [Level],
[id],
Parent,
[name],
CAST( [id] AS VARBINARY(MAX)) AS Sort
FROM parts
WHERE Parent IS NULL

UNION ALL
SELECT
[Level] + 1,
p.[id],
p.Parent,
p.[name],
CAST( SORT + CAST(p.[id] AS BINARY(4)) AS VARBINARY(MAX))
FROM parts p
INNER JOIN Parts1 c ON p.parent = c.id
)
SELECT * FROM Parts1
The above query generates the following result.

Level ID Parent Name Sort
0 1 NULL car 0x00000001
1 2 1 engine 0x0000000100000002
1 3 1 body 0x0000000100000003
2 4 3 door 0x000000010000000300000004
2 5 3 fender 0x000000010000000300000005
3 6 4 window 0x00000001000000030000000400000006
2 7 2 piston 0x000000010000000200000007
The first column shows the depth level. The last column is to keep the records in the correct order after we process all the records recursively. As you could see in the code, each level will add a binary string to the column used for the sorting. Sorting is very important in FOR XML EXPLICIT. The XML result set will be processed exactly in the same order as the rows appear in it. So we need to make sure that the result set follow the correct order that we need in the resultant XML. Let us move to the next step.

;WITH Parts1
AS
(
SELECT
0 AS [Level],
[id],
Parent,
[name],
CAST( [id] AS VARBINARY(MAX)) AS Sort
FROM parts
WHERE Parent IS NULL

UNION ALL
SELECT
[Level] + 1,
p.[id],
p.Parent,
p.[name],
CAST( SORT + CAST(p.[id] AS BINARY(4)) AS VARBINARY(MAX))
FROM parts p
INNER JOIN Parts1 c ON p.parent = c.id
),
Parts2 AS (
SELECT
[Level] + 1 AS Tag,
[id],
Parent,
[name],
sort
FROM Parts1
)
SELECT * FROM Parts2
Tag ID Parent Name Sort
1 1 NULL car 0x00000001
2 2 1 engine 0x0000000100000002
2 3 1 body 0x0000000100000003
3 4 3 door 0x000000010000000300000004
3 5 3 fender 0x000000010000000300000005
4 6 4 window 0x00000001000000030000000400000006
3 7 2 piston 0x000000010000000200000007
We did not do anything complex processing at this stage. We just renamed the Level to Tag. Let us move to the next step.


;WITH Parts1
AS
(
SELECT
0 AS [Level],
[id],
Parent,
[name],
CAST( [id] AS VARBINARY(MAX)) AS Sort
FROM parts
WHERE Parent IS NULL

UNION ALL
SELECT
[Level] + 1,
p.[id],
p.Parent,
p.[name],
CAST( SORT + CAST(p.[id] AS BINARY(4)) AS VARBINARY(MAX))
FROM parts p
INNER JOIN Parts1 c ON p.parent = c.id
),
Parts2 AS (
SELECT
[Level] + 1 AS Tag,
[id],
Parent,
[name],
sort
FROM Parts1
),
Parts3 AS (
SELECT
*,
(SELECT Tag FROM Parts2 r2 WHERE r2.ID = r1.parent) AS ParentTag
FROM Parts2 r1
)
SELECT * FROM Parts3

Here is the result set that we have at this stage.

Tag ID Parent Name Sort Parent Tag
1 1 NULL car 0x00000001 NULL
2 2 1 engine 0x0000000100000002 1
2 3 1 body 0x0000000100000003 1
3 4 3 door 0x000000010000000300000004 2
3 5 3 fender 0x000000010000000300000005 2
4 6 4 window 0x00000001000000030000000400000006 3
3 7 2 piston 0x000000010000000200000007 2

Note that we have generated the Parent Tag at this stage. We can now ignore ID and ParentID and work with Tag and ParentTag.

At this stage we have everything that we need to write the query for FOR XML EXPLICIT. Let us now write the query for the final step.

;WITH Parts1
AS
(
SELECT
0 AS [Level],
[id],
Parent,
[name],
CAST( [id] AS VARBINARY(MAX)) AS Sort
FROM parts
WHERE Parent IS NULL

UNION ALL
SELECT
[Level] + 1,
p.[id],
p.Parent,
p.[name],
CAST( SORT + CAST(p.[id] AS BINARY(4)) AS VARBINARY(MAX))
FROM parts p
INNER JOIN Parts1 c ON p.parent = c.id
),
Parts2 AS (
SELECT
[Level] + 1 AS Tag,
[id],
Parent,
[name],
sort
FROM Parts1
),
Parts3 AS (
SELECT
*,
(SELECT Tag FROM Parts2 r2 WHERE r2.ID = r1.parent) AS ParentTag
FROM Parts2 r1
)

SELECT
Tag,
ParentTag as Parent,
CASE WHEN tag = 1 THEN [id] ELSE NULL END AS 'Part!1!id',
CASE WHEN tag = 1 THEN [name] ELSE NULL END AS 'Part!1!name',
CASE WHEN tag = 2 THEN [id] ELSE NULL END AS 'Part!2!id',
CASE WHEN tag = 2 THEN [name] ELSE NULL END AS 'Part!2!name',
CASE WHEN tag = 3 THEN [id] ELSE NULL END AS 'Part!3!id',
CASE WHEN tag = 3 THEN [name] ELSE NULL END AS 'Part!3!name',
CASE WHEN tag = 4 THEN [id] ELSE NULL END AS 'Part!4!id',
CASE WHEN tag = 4 THEN [name] ELSE NULL END AS 'Part!4!name'
FROM Parts3
ORDER BY sort

Here is the result

Tag Parent Part!1!id Part!1!name Part!2!id Part!2!name Part!3!id Part!3!name Part!4!id Part!4!name
1 NULL 1 car NULL NULL NULL NULL NULL NULL
2 1 NULL NULL 2 engine NULL NULL NULL NULL
3 2 NULL NULL NULL NULL 7 piston NULL NULL
2 1 NULL NULL 3 body NULL NULL NULL NULL
3 2 NULL NULL NULL NULL 4 door NULL NULL
4 3 NULL NULL NULL NULL NULL NULL 6 window
3 2 NULL NULL NULL NULL 5 fender NULL NULL

This is what we needed. Let us apply FOR XML EXPLICIT to this query and see the results.

;WITH Parts1
AS
(
SELECT
0 AS [Level],
[id],
Parent,
[name],
CAST( [id] AS VARBINARY(MAX)) AS Sort
FROM parts
WHERE Parent IS NULL

UNION ALL
SELECT
[Level] + 1,
p.[id],
p.Parent,
p.[name],
CAST( SORT + CAST(p.[id] AS BINARY(4)) AS VARBINARY(MAX))
FROM parts p
INNER JOIN Parts1 c ON p.parent = c.id
),
Parts2 AS (
SELECT
[Level] + 1 AS Tag,
[id],
Parent,
[name],
sort
FROM Parts1
),
Parts3 AS (
SELECT
*,
(SELECT Tag FROM Parts2 r2 WHERE r2.ID = r1.parent) AS ParentTag
FROM Parts2 r1
)

SELECT
Tag,
ParentTag as Parent,
CASE WHEN tag = 1 THEN [id] ELSE NULL END AS 'Part!1!id',
CASE WHEN tag = 1 THEN [name] ELSE NULL END AS 'Part!1!name',
CASE WHEN tag = 2 THEN [id] ELSE NULL END AS 'Part!2!id',
CASE WHEN tag = 2 THEN [name] ELSE NULL END AS 'Part!2!name',
CASE WHEN tag = 3 THEN [id] ELSE NULL END AS 'Part!3!id',
CASE WHEN tag = 3 THEN [name] ELSE NULL END AS 'Part!3!name',
CASE WHEN tag = 4 THEN [id] ELSE NULL END AS 'Part!4!id',
CASE WHEN tag = 4 THEN [name] ELSE NULL END AS 'Part!4!name'
FROM Parts3
ORDER BY sort
FOR XML EXPLICIT

Here is the XML result













Adding More Levels
What do we do if we have more levels? Well, this solution is not good if you cannot guess the maximum levels that you are expecting. Most of the times we might need 5 or 6 levels maximum. In such cases this approach might work. But if you really need to work with more levels or unlimited levels, you might go with some other options, probably with a recursive function.

At present, this query is written for maximum 4 levels. Let us see how to add support for one more level. Here is how we could extend this query to support one more level. I have marked the changes in yellow which will show you how to add more levels. Using that approach, you can add any number of levels to the current query.

;WITH Parts1
AS
(
SELECT
0 AS [Level],
[id],
Parent,
[name],
CAST( [id] AS VARBINARY(MAX)) AS Sort
FROM parts
WHERE Parent IS NULL

UNION ALL
SELECT
[Level] + 1,
p.[id],
p.Parent,
p.[name],
CAST( SORT + CAST(p.[id] AS BINARY(4)) AS VARBINARY(MAX))
FROM parts p
INNER JOIN Parts1 c ON p.parent = c.id
),
Parts2 AS (
SELECT
[Level] + 1 AS Tag,
[id],
Parent,
[name],
sort
FROM Parts1
),
Parts3 AS (
SELECT
*,
(SELECT Tag FROM Parts2 r2 WHERE r2.ID = r1.parent) AS ParentTag
FROM Parts2 r1
)

SELECT
Tag,
ParentTag as Parent,
CASE WHEN tag = 1 THEN [id] ELSE NULL END AS 'Part!1!id',
CASE WHEN tag = 1 THEN [name] ELSE NULL END AS 'Part!1!name',
CASE WHEN tag = 2 THEN [id] ELSE NULL END AS 'Part!2!id',
CASE WHEN tag = 2 THEN [name] ELSE NULL END AS 'Part!2!name',
CASE WHEN tag = 3 THEN [id] ELSE NULL END AS 'Part!3!id',
CASE WHEN tag = 3 THEN [name] ELSE NULL END AS 'Part!3!name',
CASE WHEN tag = 4 THEN [id] ELSE NULL END AS 'Part!4!id',
CASE WHEN tag = 4 THEN [name] ELSE NULL END AS 'Part!4!name',
CASE WHEN tag = 5 THEN [id] ELSE NULL END AS 'Part!5!id',
CASE WHEN tag = 5 THEN [name] ELSE NULL END AS 'Part!5!name'
FROM Parts3
ORDER BY sort
FOR XML EXPLICIT


Conclusions
The approach presented in this session may not be the best possible solution. There must be other ways to do this too. I have not tested the performance factors. Based up on your specific requirement, you should make a decision about the approach to be taken. The intention of this article is to show a method, that some might find dirty and others might feel handy. The purpose of XML Workshop is to show what we could do with SQL Server XML. It does not recommend any specific method, instead it tries to present the 'XML way of doing' and you should make your own decision whether to take an XML approach or NON XML approach.