Converting XML Data to Relational Data with SQL 2005
SQL Server 2005 has many features that allow XML data to be queried and manipulated. One of the features I really like is the ability to convert XML data into relational data with a minimal amount of code. For example, if you had an RSS feed stored in a column typed as "XML" but wanted developers to be able to view it in a relational manner you could use the new CROSS APPLY syntax combined with the nodes() function:
SELECT
item.value(''./title[1]'',''nvarchar(50)'') as Title,
item.value(''./description[1]'',''nvarchar(max)'') as Description,
item.value(''./pubDate[1]'',''nvarchar(50)'') as PubDate
FROM dbo.RssOwner
CROSS Apply RawRss.nodes(''//item'')
as RssItems(item)
WHERE OwnerID = @OwnerID OR @OwnerID IS NULL
item.value(''./title[1]'',''nvarchar(50)'') as Title,
item.value(''./description[1]'',''nvarchar(max)'') as Description,
item.value(''./pubDate[1]'',''nvarchar(50)'') as PubDate
FROM dbo.RssOwner
CROSS Apply RawRss.nodes(''//item'')
as RssItems(item)
WHERE OwnerID = @OwnerID OR @OwnerID IS NULL
This example selects all <item> nodes in the RSS feed and then selects the title, description and pubDate children. The data is dynamically converted into a relational resultset making it easy to work with and bind to a variety of ASP.NET or Windows Forms controls.