Encode column values as xml in sql server 2005

The problem:

I needed to select rows from a database which corresponded to a note on a case. Each row was then written as xml to a file. Some of the columns in the row contained user entered information which could contain special characters which would create badly formed xml '<' and '>' for example. I needed a way to encode the column values when selected so that special characters were replaced.

The solution:

With a little help from the fellas over at StackOverflow.com I implemented a user-defined function which encoded the original column value. Performance wasn't a big concern as the query only ever returns one row, hence using a function;

ALTER FUNCTION [pega].[encodeTextForXml]
(
@string_to_encode varchar(1000)
)
RETURNS VARCHAR(MAX)

AS

BEGIN

declare @x xml

declare @encStr varchar(8000)

set @x = '<a/>'

set @x.modify('insert text{sql:variable("@string_to_encode")} as first into (/a)[1]')

set @encStr = CAST(@x.query('/a/text()') as varchar(8000))

RETURN @encStr

END

If I had the luxury of not being penned in by existing code, this would have been solved differently as suggested in the StackOverflow post, however it works well and does what it says on the tin!

Continual learning & getting involved

The Dean's speech at my graduation ceremony professed that this would not be the end of my learning, but in fact the beginning. Learning is a lifelong process. It was an interesting speech and one that has rung very true in my career as a developer. If you are not continually learning then you will quickly become stale as a developer, not only in terms of the code you are writing, but the tools and processes you are using to write it.

Since starting as a junior developer I have had a bit of an infiriority complex wher it comes to other developers. So after seeing a post by Jeremy Miller on CodeBetter I was inspired to do more than the odd hour of book reading that I had previously been kidding myself was enough to improve as a developer.

As part of my quest to become a better developer I have done the following;

Begun blogging - if you're reading this, you'll know all about it.
Started an out-of-work-hours side project
Attended community developer days (see below)
Begun contributing to StackOverflow
Subscribed to number of podcasts other than just Hansleminutes
Begun investigating MS Certification - Longer term goal.

Community events

One of the most productive things I have done recently to learn more is attend DDD SouthWest a free community developer event. It was an excellent event, not only in terms of the content of the presentations, but also the organisation of the day. Combine this with the fact that it was all free - even the delicious food - and its difficult to understand why there were any empty seats - ok it was on a Saturday!

The sessions I attended were;

Embracing a new world – dynamic languages and .Net - Ben Hall
Get Going With jQuery - George Adamson
Real-world MVC architecture - Steve Sanderson
What’s New In C# 4? - Guy Smith-Ferrier

Here is the full agenda which gives a brief overview of all the sessions that ran on the day as well as source code and slides.

During lunch there were a series of grok talks. Grok talks are 10 minute micro-presentations on a particular subject. Two of the ones that stuck with me were;

10 tips for speeding up sql server by Jon Reade (SqlServerClub.com)
A talk on the fututre of developing .net for mobile devices (A developer whos name escapes me)

I have been inspired to get ore involved in my local community developer events. There are lots of good ones in the Bristol / Bath area on whose mailing lists I have lurked for the past few years / months. Its time to de-lurk and get involved. Here are a few of the groups I plan to infiltrate over the coming months.

BathCamp
The .NET Developer Network
Underscore