Creating an XSD Schema in SQL Server 2005

In my previous post i showed validating xml against a schema in the SQL 2005.

Here is how you register said schema:

CREATE XML SCHEMA COLLECTION MySchema AS
N'MyXSDDefinition'

Convert XML in SQL Server

This is seriously cool.

In my C# I have read an xml file into a byte[] and stored it within the database in a varbinary(max) column.

If I wish to see this file as Xml in the database, all i need to do is the following SQL.

SELECT CONVERT(Xml, Source)
FROM MyFiles
WHERE MyFileID=7

Now what is even cooler, is if wish to validate said file against an Xml Schema, i can do the following.

SELECT CONVERT(Xml(MySchema), Source)
FROM MyFiles
WHERE MyFileID=7

So this is a really nice way of validating data in the database against an xml schema where you might not necessarily be able to used an Xml Typed column (accepting multiple file types)

SQL 2005 XQuery Modify

I stumbled across this issue today.

Simply I want to update all rows within my xml, where Organisation="Default", and replace it with the default organisation id.

Unfortunately with .modify in sql you can only modify a single element. The syntax is a little weird but here is my solution.

DECLARE @myXml Xml

SET @myXml = '<Customers>
    <Customer Name=''Fred'' Organisation=''Default''/>
    <Customer Name=''Bob'' Organisation=''Default'' />
</Customers>'

    -- Update Roles
    DECLARE @iNodeCount int
    SET @iNodeCount = @myXml.value('count(/Customers/Customer[@Organisation=''Default''])','int')
    DECLARE @iCurrentNode int
    SET @iCurrentNode = 1


    WHILE (@iCurrentNode <= @iNodeCount)
    BEGIN
        -- Update the root element with the default chargeband
        SET @myXml.modify
        ('
            insert attribute OrganisationId {5}
            into (/Customers/Customer[@Organisation=''Default''][sql:variable("@iCurrentNode")])[1]
        ')

        -- increment the loop
        SET @iCurrentNode = @iCurrentNode + 1
    END

SELECT @myXml

SQL Server 2005 Synonymns

I think this is probably one of the most useful features of SQL Server 2005 (and certainly one of my favourites).

A synonymn allows you to expose a stored proc, table, function etc, locally within in a database as a pointer back to another database (which can either be on the same server or a remote server).

This functionality really is brilliant for scaling database, and allows you to easily seperate functionally seperate database but still harness the power of common functionality. This allows you to get out of the mindset of lumping all your tables into one database and build an approriate solution.

Here's how you do it

CREATE SYNONYM [schema].[localtablename] FOR [databasename].[schema].[remotetablename]

Simple huh e.g

CREATE SYNONYM [dbo].[MyCommonStoredProc] FOR [MyCommonDB].[dbo].[MyCommonStoredProc]

Hope this helps, I certainly love it

Silverlight Southampton Slides

I've posted up my slides for my silverlight in southampton session last night, you can download them from my Skydrive

Back from Southampton

I'm back from last nights Silverlight Session in Southampton.

I spent 2 full hours live coding the session. No Slides, Virtually No Cutting and Pasting. Just pure coding. And from what I can tell it went down a storm. I really enjoyed the event, much preferred the new format to the sessions I have done in the past. I also think everybody enjoyed seeing everything being coded live.

We really covered a lot in this session, and suprised that i managed to pack so much in. There were a couple of hitches (one of the things with live coding, no matter how much you prepare), and again I have a couple of lessons learned, but I am really really pleased with this session.

I think it safe to say, I can put my disastrous DDD sessions behind me. I have now done 3 main sessions since then, and they have all went really well. One of them in is currently in the NxtGenUG all time top 10.

I have only been speaking since september, and every session is a learning experience, and I think I am improving each time.

Just to say a big thanks to Rich and John at Southampton for supporting a relatively new speaker, and well done guys, what a great group you guys have (very impressed).

Also a big thanks to Guy Smith-Ferrier for putting me forward to John and Rich to do a session in Southampton. Guy really is one of those superb blokes who really wants to develop UK speakers. After DDD i really felt as if I let folks down (the feedback wasn't that bad, but i knew i could do much better), and I really wanted to get this one right.

Anyways enough waffle.

Thanks Southampton for coming to the session, and the code will be up later today

Silverlight in Southampton

Tonight I'm off to the coast, ice creams and bucket&spade for me.

That's right I'm presenting Silverlight for NxtGenUG in Southampton.

I've not been down to southampton yet but I have met the coordinators (John and Richie), who are both top blokes.

If you're in the area you should still be able to register.

Silverlight Firestarter Videos

A few months back Microsoft did a firestarter event in Redmond on Silverlight.

For the vast majority of the world who didn't go to this event (including me), they recorded it, and stuck the videos online. You can download the videos from the mix site

Not only are the videos good, but the website itself is a nice use of Silverlight. There is some great introductory material on the videos (and is a nice thing to watch on your ipod/zune as some gentle relaxation).

If you are looking for a good introduction to Silverlight, this is one of the best places to start.

One of the seriously cool things about these videos are the actual speakers. You have some of the top microsoft bods in Silverlight and WPF speaking at this event (Jesse Liberty, Laurence Moroney, Adam Kinney, Adam Nathan, Ernie Booth), this means you really do get some useful insights into where everything is going.

So even if you are pretty experienced with Silverlight it is worth watching these videos.

Developer, Designer Interaction

With Silverlight 1.1 (2.0 in the future), the holy grail of developer / designer interaction is infeasible (with WPF it is perfectly possible).

Even when it is possible, there is a mindset which will need to be instilled to make this sort of progression.

The holy grail is to be able to hand over as much of the design (to the designer), so the developer can work on backend code and leave the designer to be responsible for design changes. The real trick is giving the designer the ability modify the UI without impacting/annoying the developer (and vice versa).

In the Silverlight 1.1 (as it is), if for example you wished to change the color of a textblock in a mouseover. UI code will endup in your codebehind, which will be interlinked with the design. If the designer makes a change to the color (or decides to use LinearGradientBrush rather than a SolidColorBrush), the developer will have to change his/her code. As developers (in a 2.0 world), we will have to change our mindset to allow good interaction.

UI Code such as this must be kept within the XAML layer (and not invade the codebehind). We will be able to do this by using techniques used in WPF such as styles and routed events etc.

Once the 2.0 beta is released, this area should definately be revisited, so developers can avoid these mistakes from the outset.

Happy New Year

So i'm back from my Texas (having a well earned New Years break) (Go Spurs Go).

As it's a new year, I hope to get together some of the stuff I have been speaking about (Silverlight), and put together as screencasts and make available in my blog.

I should start making some of these available by the end of this week.

I will probably be starting from the very beginning, and working up to the more advanced features. These screencasts will have a 2.0 (1.1) focus, and will be mainly from Visual Studio 2008.