On Thomas Frank's blog there is an interesting post which shows how to get JSON from MySql, and I though about doing the same to SQL Server since I use it more than any other database lately. So what I did was create a stored procedure which uses cursor to format the string with our data. I'm not really good with SQL, so I managed to use two, and the procedure itself isn't really what you would call a masterpiece. Sorry about that. But what I want to show here is just a concept tho.
Here's the Procedure.
When I execute it like this:
exec getJson 'announcement', 4
I'll get a single column named 'json' with *all* my data, in this case, 4 registries from a table named announcement. Something similar to this:
[{'Id':'1','Title':'<h3>1. Lorem ipsum dolor sit a','Date':'Jun 16 2008 5:44PM','Text':'<p>Lorem ipsum dolor sit amet,'},{'Id':'2','Title':'<h3>2. Quisque aliquet accumsa','Date':'Jun 16 2008 5:44PM','Text':'<p>Lorem ipsum dolor sit amet,'},{'Id':'3','Title':'<h3>3. Duis consequat laoreet<','Date':'Jun 16 2008 5:44PM','Text':'<ul><li>Duis consequat laoreet'},{'Id':'4','Title':'<h3>4. Aenean consectetuer</h3','Date':'Jun 16 2008 5:44PM','Text':'<p>Quisque aliquet accumsan le'}]
Then if you're using one JavaScript widget to slide news/announcements, it might be useful. Just pass this returning text to the eval method and you have a fresh json object, ready to use.
NOTE: it shouldn't be used with large amounts of data since you're populating a varchar sql variable, and it has a limit. If you try to pass more data than it can handle, you'll get errors.