json – sqlsunday.com

My brand-new T-SQL reference app for Slack

If you haven’t heard of Slack, it’s a wonderful team messaging platform. At first glance, it looks a bit like a private Twitter where you can set up “channels” to have conversations with colleagues. But the great thing with Slack is its flexible API and all the marvelous ways in which you can extend its functionality. You can send rich text messages with status updates from your production servers or you can interface with popular web services like Trello, right there in the chat window!

So as a way to kick start an effort at learning node.js and re-discover web development (which I haven’t really done in about 15 years now), I set out to build a Slack API. Here’s what I learned.

Converting JSON data to XML using a T-SQL function

Depending on your line of work, you may some time stumble over JSON data. JSON is a data format specifically designed to work well with Javascripts on web pages, so it’s fairly common in web service applications. If you’ve managed to land JSON data on your server and need to convert it to XML, here’s an idea how to.

Web requests using a CLR procedure

For most things that you can’t do straigh-off in T-SQL, you have the option to write a Common Language Runtime procedure. This gives you access to a huge array of APIs, so you can create really cool stuff, way beyond what T-SQL would normally allow you to do. Here, we’ll explore how to write a web request procedure in C#, so you can download a web document (for instance, a file on a REST service) directly into a table or a string variable using just a regular function call.