A simple C# JsonConverter example – bool to bit

Newtonsoft Json.NET is a great framework for serialising/de-serialising your Json with .Net, it has a good collection of default handlers for transforming data-types, but what if you would like to override the way in which a property is written/read.
In this example we will change the way that boolean properties are handled, so that they are read and written as 1 or 0 as opposed to the default “True” or “False”.

Continue reading

T-SQL: Casting Varchar to XML removing illegal characters

As I mentioned in an earlier post, we deal a lot with XML messages on ActiveMQ JMS queues going through our ESB.  For audit purposes most data that goes through the ESB is logged at some point, including these XML messages.

These XML messages have been stored, historically, in fields of type varchar, so if we want to use XPath to query the XML, we need to cast the varchar values to XML first.

Unfortunately, there are illegal characters in our XML, so we can’t just CAST(… AS XML), we have to replace those characters first.

As I am a sharing kind of guy, here’s a snippet to help others…

Declare @cr Char(1) = char(13)
Declare @lf Char(1) = char(10)
Declare @tab Char(1) = char(9)
Declare @pound Char(1) = char(163)
Declare @uScore Char(1) = char(150)
Declare @regSymbol Char(1) = char(174)
Declare @tmSymbol Char(1) = char(153)
Declare @f1Quote Char(1) = char(146)
Declare @b1Quote Char(1) = char(145)

CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([MyXMLField], @cr, ''), @lf, ''), @tab, ''), @pound, ''), @uScore, ''),@regSymbol,''),@tmSymbol,''),@f1Quote,''),@b1Quote,'') AS XML)
FROM ...

Please let me know in the comments if you have come across a cleaner solution to this problem, or if it needs updating with extra characters.

First foray into the lair of the Mule…

Around a year ago I started a new job in the motor insurance industry, with a company that is part of the Lloyds Insurance syndicate who only do business through insurance brokers. These brokers send in details of new business, mid-term adjustmment, renewals & cancellations, claims.  Then this is complicated further by us providing breakdown cover too.

Now, you might think that this doesn’t sound too complicated, give them a schema and ask them to send files to match that schema, or provide them with an API that they can hook into… nope! Continue reading