OPML to ASP.NET 2.0 Site Maps via SQL Server 2005 XQuery
The title alone reminds me of an Ole and Lina joke, but no, seriously, I needed to generate a section of links for an ASP.NET 2.0 Web Site I'm working on out of an OPML export of blogs related to a given topic. Now, sure, I could have done this some hard way, like with XLINQ or XSLT, but why bother when we have the simplicity of XQuery at hand in SQL Server 2005. Consider the following query:
set @opml ='<?xml version="1.0" encoding="ISO-8859-1"?>
<!-- OPML generated by UserLand Frontier v9.0 on Fri, 23 Jul 2004 23:41:33 GMT -->
<opml version="1.1">
<head>
<title>ourFavoriteFeedsData.top100</title>
<dateCreated>Fri, 02 Jan 2004 12:59:58 GMT</dateCreated>
<dateModified>Fri, 23 Jul 2004 23:41:32 GMT</dateModified>
<ownerName>Dave Winer</ownerName>
<ownerEmail>dave@userland.com</ownerEmail>
<expansionState></expansionState>
<vertScrollState>1</vertScrollState>
<windowTop>20</windowTop>
<windowLeft>0</windowLeft>
<windowBottom>120</windowBottom>
<windowRight>147</windowRight>
</head>
<body>
<outline text="Scripting News" count="580" xmlUrl="http://www.scripting.com/rss.xml"/>
<outline text="Wired News" count="546" xmlUrl="http://www.wired.com/news_drop/netcenter/netcenter.rdf"/>
<outline text="Boing Boing Blog" count="519" xmlUrl="http://boingboing.net/rss.xml"/>
<outline text="The Scobleizer Weblog" count="460" xmlUrl="http://radio.weblogs.com/0001011/rss.xml"/>
<outline text="Slashdot" count="347" xmlUrl="http://slashdot.org/slashdot.rss"/>
<outline text="Joel on Software" count="328" xmlUrl="http://www.joelonsoftware.com/rss.xml"/>
<outline text="Jon's Radio" count="325" xmlUrl="http://weblog.infoworld.com/udell/rss.xml"/>
<outline text="Google Weblog" count="321" xmlUrl="http://google.blogspace.com/index.xml"/>
<outline text="Jeffrey Zeldman Presents- The Daily Report" count="312" xmlUrl="http://www.zeldman.com/feed/zeldman.xml"/>
<outline text="kuro5hin.org" count="301" xmlUrl="http://www.kuro5hin.org/backend.rdf"/>
<outline text="Dilbert" count="297" xmlUrl="http://dwlt.net/tapestry/dilbert.rdf"/>
<outline text="Gizmodo" count="284" xmlUrl="http://www.gizmodo.net/index.xml"/>
<outline text="Joi Ito's Web" count="276" xmlUrl="http://joi.ito.com/index.xml"/>
<outline text="The Doc Searls Weblog" count="245" xmlUrl="http://partners.userland.com/people/docSearls.xml"/>
<outline text="A List Apart: for people who make websites" count="231" xmlUrl="http://www.alistapart.com/rss.xml"/>
<outline text="The Shifted Librarian" count="226" xmlUrl="http://www.theshiftedlibrarian.com/rss.xml"/>
<outline text="Six Log" count="222" xmlUrl="http://www.sixapart.com/log/index.rdf"/>
<outline text="Tim Bray" count="220" xmlUrl="http://www.tbray.org/ongoing/ongoing.rss"/>
</body>
</opml>'
select @opml.query('for $feed in (/opml/body/outline) order by $feed/@title return <siteMapNode url="{$feed/@xmlUrl}" title="{$feed/@text}" description="{$feed/@text}" />')
And somehow I supposed to believe that XLINQ is easier than that? :)