About the Author

Paul is currently a software developer at InterKnowlogy, a leading edge developer of custom software solutions on the Microsoft platform, and an alumni of Neumont University with a Bachelors of Computer Science. Professionally at InterKnowlogy, Paul is responsible for developing and maintaining cutting edge applications for various clients. This primarily includes work with XAML (Which involves both WPF, Silverlight, and Windows Phone 7), C#, and SQL in addition to a large number of related or supporting technologies and tools. In his spare time Paul writes code, blogs, speaks at various events, takes pictures, attempts to surf, goes hiking, plays Starcraft II, spends time exploring southern California, helping out with his church, and hanging with friends and family. Life rocks.

Typesetter HD

Just a quick heads up, I updated TypeSetter to work with high density displays. So if your on an iPhone 4 or 4S, or any other device that has a non standard pixel ratio and uses the baseline script it will show up clean and sharp.


Here’s a sample with the updated grid drawing code:

Back to HTML – Tricky margins

HTML layout is weird. Half the time it makes sense, and the other half it makes half as much sense as it should. To that end, here’s a quick refresher on margins and padding and layout that I’m writing as much for myself as for you.
Lets start with a box. In this imaginary real box wold, you are either putting blocks next to each other, or inside other boxes, to get elements to line up and appear in some fashion or another. These virtual boxes have two properties, margin and padding. In our box world analogy, the padding would represent the thickness of the walls of the box. If you add something thats bigger than the inside of the box, the actual outside edge of the box will have too expand to contain whatever is inside. When you set a margin however, its simply stating a rule that you would like this box to be at least x number of units away from any other sibling box.

For instance, I have Box A and I want anything around Box A to be at least 2 feet away on the top or bottom of the box. Now, I have Box B that also has margin, but for Box B I only need everything to be 1 foot away along the top or bottom of the box. If I placed these two boxes next to each other (as siblings), the boxes would be 2 feet apart. Box A overrules Box B since A requests any other box to be further away from B, and thus B is also satisfied since the margin for B is less than A. If I also specified padding for box B of 6 inches, the contents of B would be at least 2 feet 6 inches from A since margin is measured from the outside edge of the box.

Here is a real example. In this case, I have 2 paragraphs. The orange space represents margins, and the green represents padding as shown by the debugging tools in chrome.

The code looks something like this:

     <p>This is some paragraph text 1</p>
     <p>more paragraph text</p>

Example 1 Margining:

Notice how the margins collapse when they touch each other. Remember that margin represents ‘minimum spacing’.

Example 2 Padding:

Notice how not only are the paragraphs further apart with similar spacing, but they padding doesn’t overlap at all. This distinction is important when you lay out content, and especially when you start floating things. Eventually the imaginary real box model starts to break down as we approach real world HTML. Another really important tidbit to understand is that margining can extend OUTSIDE of the container. For instance, in the previous code snippet, we only have one article and two

tags. If we put two articles side by side, we can see how the paragraph margining will extend out of the article block:


     <p>This is some paragraph text 1</p>
     <p>more paragraph text</p>
     <p>This is some paragraph text 1</p>
     <p>more paragraph text</p>

Note how the article is only the size of the paragraphs inner dimensions:

However, when we look at the paragraph margining, we see:

The paragraph spacing extends beyond the bounds of the article and pushes the paragraphs and the article down. I know. The box model doesn’t work any more. Remember the differences and play around with it yourself.

Some Practical Advice

  • When you first start laying elements out on a page, only specify bottom margining and keep the space consistent. It will help debug initial issues since there will be less overlapping margins.
  • Use a sylesheet language like less or sass so you can keep the numbers more consistent and easier to change.
  • Use good debugging tools. Chrome Developer tools, or Firebug are both good. They’ll keep you from going insane immediately.
  • If all else fails, simplify the problem. Make a small, quick html page to try to replicate your scenario.
  • Tinker. You only learn something by doing it. Do it.

Tip: Using zoom to target images to high density displays

I found this several days ago when I was looking for a way to scale images on an iPhone inside of a fluid layout such that they would display at a 1:1 pixel ratio. If you didn’t already know, the iPhone 4 and 4S displays everything as if the screen was the same size as a normal iPhone when it measures and does the layout. The result is that all the text looks extremely crisp, and any sites already designed for the original iPhone don’t break or look weird. With images this means that a 300 x 300 pixel image displays the correct size in the layout, but is essentially scaled up to fit over 600×600 “physical pixels”. As I was poking around I found an old CSS zoom property here: http://reference.sitepoint.com/css/zoom that for some reason works on mobile safari. Add a media query for 2x density screens, and viola! Your images now display at physical pixel dimensions on the iPhone 4 and 4S.

Here’s what the CSS looks like:

@media screen and (-webkit-min-device-pixel-ratio: 2) {
     img {
          zoom: 50%;

Awesome hacks.

Happy img scaling.

On Modern Layouts and Semi Fluid Grids

Over the last several weeks, I’ve really been digging into HTML5, CSS3, and really figuring out what modern browsers can do. Frankly browsers have grown by leaps and bounds over the last several years, and as such we need to re-evaluate how we build websites. Especially in regards to how we treat older browsers. I recently read this post by Paul Irish and he points out that ideally, each browser gets an experience that is customized to that browser’s capabilities. What this means however, is that we should expect older browsers to get an experience that isn’t exactly the same as the experience we get on a modern one. (Example, no rounded corners. No text shadows. Etc…)

Let me try to explain:

A really good analogy to this is how HD TVs have changed the TV industry. Imagine for a moment that a website site is the film, and the TV is your browser. If you had a black and white TV, you wouldn’t expect to see the film in color. If you have a standard definition TV you don’t expect to see the full wide screen movies, and if you have a widescreen 720p TV, you don’t expect to experience the full effect of 1080p Blu-ray. Filmmakers don’t shoot in standard definition and then try to ‘upgrade’ the film to HD quality. Its pointless and a LOT of extra work. No, they shoot with the best film and resolution they can afford and then cut it down to the lower denominators when or if they support older formats.

In the same way, if you use an older browser you wouldn’t expect to get the full experience a modern browser supplies, and moreover, you likely wouldn’t even know what your missing! You don’t expect it since any other site you visit also doesn’t have those features or details. Because of this, you should be designing for the ‘1080p’ of the web, and accept that users with older browsers will still get the ‘standard definition’ experience.

The Web Is Not Print

There’s also an important distinction between print and the web. The web is dynamic and fluid, highly interactive. The web is also delivered on a range of sizes and devices, from something the size of a business card up to large 30″ monitors. There comes a point where form and function have to merge in a non-trivial way to provide an experience that fits itself to the users needs, when they need it, the way they need it.

Print isn’t.

Print is extremely static, once it’s designed, it stays that way. If you design a poster, you design it to be printed at a certain size or at certain proportions. If you design a book or a magazine, you have total control over the size and layout of your spread. If you continue to think about the web this way, you’ll miss much of the dynamic nature of the web that makes it attractive as a medium.

Its only recently we’ve really gained the tools and techniques we need to REALLY break out and away from the classic printers layout due largely to the rise in mobile devices, HTML5 and CSS3. What I’m calling this “Print Format” tends to reveal itself at the extreme edge cases.


  • On large monitors where content will appear tiny and hard to read with lots of extra white space.
  • On small monitors or mobile devices when the content doesn’t scale down and your forced to scroll back and forth to read.

Over the years one of the only real solutions to address this, if someone addressed it at all, was just to never set a width for your content and just let it fill your entire screen. Although this almost works for some things, it’s difficult to read as paragraphs will stretch across the screen. You will constantly lose your place as your eyes track back and forth to the beginning of the next line. Also, if your designing a static site and you and your designers both have large screens, it’s easy to ‘fill up the space’ with ancillary or useless content and ads to make the site feel balanced. All in all, most so called solutions were hacks on a broken system.

Semi Fluid Layout

I’m finally starting to see some CSS frameworks and sites that are really starting to apply these principles. An excellent site that demonstrates this principle is the following:


No matter what browser you visit it on, or what size your browser is at, the content will be sized appropriately, the typography will be readable, and everything will feel right. Sans insane urges to resize your browser window.

One of the key benefits to designing a fluid theme is the instantly correct look you get when you open the site. Its not something people will or should consciously expect, it should simply BE the correct size the instant it appears, regardless of size, screen, or orientation. Its like walking into a store. A well designed building is easy to navigate, spacious, and consistent across all other stores you visit regardless of size. You don’t ever think about it, you’re not there to analyze the layout of the store. You’re there to buy groceries. Anything that gets in the way or makes it hard to find what you’re looking for will stick out, and if it’s bad enough, you’ll go somewhere else. Its the same way with a site or blog. Users are there for a reason: For information, or to read, or to consume whatever content you are serving up. They didn’t come to analyze your site and how it’s designed, but if they have any difficulty at all in getting to what they want in whatever form they want it in, they’ll be somewhere else in about 8 seconds.

It’s also important to be intentional about the decisions your making and how they will affect the final layout of a site. The more you reduce the complexity of the interactions, and the better defined the interactions are, the better the final end result will be. Use what you design. Do what your users do. You’ll know best what you do and don’t like and what feels right. Go with it, but don’t be afraid to stop, backtrack, or even completely scrap good ideas if they don’t fit with what you expect from the design.

Fluid Layout First

Something that’s going to become really important is to start out your design efforts knowing your designing for a fluid site like this. Laying out the groundwork and page structure is going to be extremely important to your long term sanity as a developer. There’s this concept in computer science called emergent behavior, essentially, the less you specify about a system, and by imposing fairly simple rules you can get easily get complex behavior. By starting with the containers, and then working your way to the content I have much less to worry about since most of the content takes care of itself without much more intervention from the developer.

If your someone who already has a well established, static width layout, its going to be difficult to establish a well behaved fluid grid system without tweaking MANY aspects of the site. Its probably why a lot of companies simply serve up an entirely different set of HTML and CSS for the site for mobile devices: Its easier to do than to rebuild their main site to scale all the way down to small screens.

Entity Framework Self Tracking Entities Cascade Delete Fails

This is just a quick FYI if you come across a situation while using self tracking entities where you’re getting weird errors when trying to delete a entity that had cascade delete turned on for related entities.

Instead of doing:



You need to first make sure the entity is attached and then call DeleteObject for the cascade to run correctly.

So now the code now becomes:


Happy cascade delete.

Fonts and the Web

Here at InterKnowlogy, we get time each week to tinker and dive into technologies that interest and inspire us. We call it RECESS, which stands for Research and Experimental Coding to Enhance Software Skills. This week I’ve been noticing sites ‘sprucing it up’ by using custom font embedding with services such as TypeKit (Or similarly related projects such as Google Web Fonts) that allow you to license and use custom fonts in a site.

There’s a problem though. 90% of the time it annoys me.

Let me explain. First impressions are everything, and right after first impressions is readability. If I come to your site looking for information, to read something, I don’t want to get a headache doing so. The problem isn’t the design or the font itself usually, it’s the way the browser deals with and renders the font. Using a custom or weird font for the body text of the page WILL bother people. Its why standard fonts exist and are as popular as they are: readability. Now, that being said, there are really good design reasons to want to use custom fonts in a dynamic content driven site to augment the design. Especially in the title and headers since it will catch someones eye and because people spend a minimal amount of time reading them anyways. Whatever the reason, if your going to go down the custom font path its going to be important to refine and choose a solution that is going to be consistent and usable for the people that visit your site.

Coming back to RECESS, I spent some time examining the different offerings and essentially broke them into two categories: Browser/OS rendered text, and Image rendered text. Let me explain: For the Browser/OS rendered text (which TypeKit and Google Web Fonts use) the text is set using normal CSS rules, and then an actual font file is loaded by the browser and used to display the text. Almost exactly how text is displayed for application on your computer except that the font is never ‘installed’. The advantage is that it behaves exactly like normal fonts, you can type, select, copy, and do all the normal things you do with text. The second way is imaged rendered text, taking some chunk of text and turning it into an image that gets displayed in place of the text. Designers sometimes do this for logos and main headers that almost never change because it doesn’t require the use of a more modern browser and they KNOW that it will display the same way. Also in this category is a tool called Cufón. It’s a bit of JavaScript and a bit of a generator. It basically takes a font file, turns it into a bunch of shapes, and then on the users computer it uses JavaScript to load, render, and replace text in the page with those images. The disadvantage being you can’t select and copy the text in the same way, but the advantage is that it looks consistent and renders well.

I decided to tinker with these and see what I came up with (All screenshots are on Windows 7, the IE9 ‘font’ example is invalid as I would have had to convert it to a different format for it to display, but I was lazy. There is another example later that illustrates IE9 correctly showing a font this way.)

So here are some screenshots of the results on different browsers on Windows 7:

Chrome 12:

Firefox 6:

Safari 5.1 on Windows

Internet Explorer 9

I was surprised at the results. Same font, same file, but completely different results between Chrome / Firefox / Safari (IE9 excluded obviously) for the regular font rendering. Cufón came out the most consistent of all of these.

Next I went to TypeKit and found excellent illustrations of why embedding fonts is still so difficult and why I’m seriously considering using Cufón for the time being:

From top to bottom, Safari 5.1, Chrome 12, Firefox 6, IE9

The big thing to note is how jagged the letters look until you get to IE9. Readability wise, I would NOT consider using an alternate font for a large amount of text unless I had to, and for right now, Cufón seems a very viable choice for consistency.

There’s no final conclusion in this, since you will have reasons that will drive you to one option or another (Or simply throw your hands in the air and announce to the world that you are done with the web forever) So:

Nerd out.


Be careful with your font choices and how far you take this on a site that will be used on a regular basis. Cheers!

Merging a WPF application into a single EXE

I always dislike handing off little applications to people. Not because I can’t, but because of the steps involved to make sure it all just works. Small apps are the most problematic because I never want to take the time to create a whole installer project for just a few assemblies, and packaging up a zip file must be accompanied by “Unzip this into a folder in your programs directory and create a shortcut…” which brings us back to the whole installer business we started with.

There are a few tools already out there such as ILMerge by Microsoft Research (Which works great for most .NET-y things, but chokes on WPF applications) and a few paid tools by third party vendors that you could fork over a few hundred for to get. But, I’m a developer, which means I want to do it the Hard Way™. I did a little research and found the following blog posts on setting up and merging in DLL’s as resources into the main assembly and then extracting and loading them into memory when you run your application.


There were a few things I didn’t like about each solution. The first one (richarddingwall.name) ends up having you directly adding the .dll’s as resources directly. I hate maintaining things manually, especially when it will run fine on my machine but break when when I move it somewhere else because I forgot to update the resources when I added a new project. The one from blog.mahop.net builds on the previous one and changes the resolve location to a custom class with its own startup method. Better, because it resolves the resources earlier. Finally, the one from Daniel Chambers (digitallycreated.net) added in the final piece that automatically including the assemblies as resources. Unfortunately, the way he looks for culture specific assemblies didn’t work and I had to remove / change it to be closer to the one on mahop.net.

Final solution I’m currently using is as follows:

To the main executable project, unload and edit the .csproj file, and below the following line:

<Import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" />

Add this XML to the project file, save, and load it back up.

 <Target Name="AfterResolveReferences">
    <EmbeddedResource Include="@(ReferenceCopyLocalPaths)" Condition="'%(ReferenceCopyLocalPaths.Extension)' == '.dll'">

It should look something like this when your done:

You’ll then add a new code file to the main project and add the following code to it (modified to fit how your application is named / structured):

        public static void Main()
            AppDomain.CurrentDomain.AssemblyResolve += OnResolveAssembly;

            App.Main(); // Run WPF startup code.

        private static Assembly OnResolveAssembly(object sender, ResolveEventArgs e)
            var thisAssembly = Assembly.GetExecutingAssembly();

            // Get the Name of the AssemblyFile
            var assemblyName = new AssemblyName(e.Name);
            var dllName = assemblyName.Name + ".dll";

            // Load from Embedded Resources - This function is not called if the Assembly is already
            // in the same folder as the app.
            var resources = thisAssembly.GetManifestResourceNames().Where(s => s.EndsWith(dllName));
            if (resources.Any())

                // 99% of cases will only have one matching item, but if you don't,
                // you will have to change the logic to handle those cases.
                var resourceName = resources.First();
                using (var stream = thisAssembly.GetManifestResourceStream(resourceName))
                    if (stream == null) return null;
                    var block = new byte[stream.Length];

                    // Safely try to load the assembly.
                        stream.Read(block, 0, block.Length);
                        return Assembly.Load(block);
                    catch (IOException)
                        return null;
                        return null;

            // in the case the resource doesn't exist, return null.
            return null;

Finally, make sure you update the target method for your main application to be the main method for the project you just added:

And, that’s it!

When you build your application you’ll still see all the assemblies in the output directory, but you should be able to take just the executable, move it somewhere else, and run it just as it is.

An Introduction To Web Fonts

Back in the old days of the Internet, circa 10 years ago, people were just beginning to discover all the new cool things you could do with the web. Print was still around in full force in all its forms of magazines and newspapers. We even had individuals that made a living putting together and curating text based content. Over the years as more and more content has migrated to the web, one of the major limitations designers have faced is that they only had about 3-5 fonts that they could reliably use to format their text and be sure that the particular font they had chosen would exist on the users machine. Sometimes there would be variations, but those variations could never be guaranteed to render the same way in a users browser. Print was just extremely flexible and could provide a typographical brand and experience that the web simply couldn’t match.

As the web has continued to evolve through the browser wars and the waves of ‘web 2.0’ there was still very little change in the way text was shown in the browser. Over the last few years some of these issues have begun to be addressed, and there’s a growing amount of support in more modern browsers to express this new idea of web fonts. The core issue is that everyone is running on a different browser, different operating system, platform, or device, and each unique configuration has its own typographical settings, fonts, defaults and so on. Websites initially began experimenting with solutions to solve this issue by actually embedding a link to the font that the browser can recognize, download, cache, and use to render fonts it doesn’t already possess. But this also posed a problem: Type foundries don’t simply want their fonts to be embedded and freely downloadable and usable by whoever happens to come along and visit a site that uses one of their fonts. A font foundries whole business model is based around the licensing of the fonts they create, so having something ‘freely downloadable’ would never appeal to them.

As I have been doing some exploration on the latest and greatest of web technologies related to HTML5 for RECESS here at InterKnowlogy, I wanted to share two different approaches that I’ve found of companies that are exploring this somewhat new idea of web fonts. Chances are you may have even seen sites already that use this, but didn’t realize it or how they did it.

TypeKit – http://www.typekit.com

Google Web Fonts – http://www.google.com/webfonts

Each of these take two separate approaches that may or may not be viable to you as a designer / developer / company depending on your needs. TypeKit is arguably the more powerful and flexible options of these two, but is a paid service (although very reasonably priced). It has a large portfolio of professional grade fonts from many different foundries, including some many of the main fonts that are included in Adobe products (which I’m sure is a plus for many designers). They uses an internal obfuscation and customization process to protect the individual font from being used outside of the website that the license was purchased for. This has allowed them to negotiate deals with some of these foundries to license the fonts for web use through their company (and they’ve obviously been decently successful).

Google on the other hand, has opted for the freely available open source fonts. All the fonts used in its web fonts site are free to use for anything and are supported on a purely donational basis. This provides two advantages: All the fonts are freely available, and a single browser can cache a font and use it across any sites that also use that same font without redownloading it, this means there will be less overhead and a more consistent experience across sites that use Google Web Fonts. However, arguably the biggest downside is that the selection of fonts is much more limited. Google has done a excellent job of curating the available fonts it has to ensure that they meet some internal quality standards, but likely many of the industry standard fonts will never be available in Google’s catalog because of licensing issues.

There is some very cool things you could do with this, I’ve purposely left out many technical details in order to give a more general overview of web fonts. Hopefully this has been enlightening and has sparked some creative ideas for cool new things you could do with this technology.

Happy Typing!

Undo Forever – Version control for the rest of us

You’ve run into it before. You fire up a new project, write some code, leave, come back, and a week or two later decide the approach was all wrong. Code becomes a mess. We try one thing, then another, go back, try again, break things, experiment, wash rinse and repeat. You work on it for a few more days, and realize you were almost right the first time, and now you want to go back. What do you do?

This talk will focus on using GIT as a local SCM (Source Code Management) tool to help alleviate some of the problems we as developers face on a day to day basis in various situations. No cost, no server, no hosting, local versioning, undo forever.

Sanity is a good thing.

And so begins my talk on Git at the first SoCal CodeCamp of 2011.

I know most of you have run into one of the following situations before.

  • Your on a large project, and you hate checking in because you might break the build.
  • You work on small personal projects (Your in a programming class and have assignments to do) and setting up and or using subversion sounds like a complete waste of time, yet you wish you could never close visual studio so you could ctrl+z back to wherever you wanted.

Bear in mind, almost everything I talk about today can be replicated in some fashion by most of the Version Control Systems out there already. Regardless, my goal is to give you something you can take home today and use tonight to help relieve some stress from pain points you may never even knew existed because you were so used to it. Even just to take the ideas and apply them to your current situation and workflow.

Git was created in 2005 back when the Linux kernel lost their licensing for BitKeeper, and something was needed to replace it besides patches and tarballs. Linus, finding no good versioning system out there, created his own and released it to the community a few months later calling it git. It has several unique features:

  • Distributed (which means every repository is a full copy of the entire repository)
  • Efficient handling of large projects (Git is SUPER fast)
  • Cryptographic authentication of history
  • Snapshots entire tree’s instead of individual files

College Dev Story

Sweet. What does this mean for you? Ok, you’re a college student again. You have an project you already thought you completed but find out there’s a new requirement that require a bunch of changes to your code. You think you know what you need to do. If your like me (because this actually happened to me) you pull out the project, maybe make a copy of it, and start partying on the code to meet the new requirement. Two days later, you wish you could go back one day ago, because you introduced some code and made some refactors that are definitely not going the way you wanted. Trouble is, the last copy of your code (if you actually made the copy) was two days ago when you started. Fail.

If your also like me, you also find it a paint to set something up, I really don’t have any desire to install subversion, make a free account somewhere (or God forbid, setup my own server, configure the dns so I can access it offsite, deal with checkout / checkins) because its a school project. Who CARES about a small project when the setup to version it is a pain? So here’s where I wish I knew about something like Git, for this simple feature: I can version my files locally, without a server, without a network, without the version control system interfering with however I would like to edit my code. (Subversion adds a bunch of .svn directories, TFS makes all your files read only (Try saving a file with notepad, it sucks)) Creating a new repository with Git takes less than a second, doesn’t touch any of my files, and when I commit, it tracks all my changes by the cryptographic hash of the content (No more worrying about if my change accidentally got missed).

It would have made it much easier to track my assignments, all of them, even the little scratchpad apps I created to test things and experiment.

Enterprise Sized Dev Story

Now, the other story. In this story you’re a developer on a large project. Say there’s about 30 people working on the same project, broken up into 3-4 teams, each working on a different portion of the application, using a centralized version control solution like subversion or tfs. Everything is fine and dandy until someone checks in something that breaks the project. 29 developers get latest, hit ctrl+f5 in visual studio, and the build fails. So, management gets involved, and now there’s a build server, and a checkin policy that the project has to build using something like the build tools with VS team edition or something like Cruise Control. This goes well for a while until people start realizing that as the systems interact that functions that they thought were stable are suddenly being used by other people and returning values they didn’t expect. So e-mails fly, and now the build server has unit tests and a build validation with unit that must pass.

There’s nothing inherently wrong with having a central server to store all your code, nor is having unit tests or build validation. However, to you and me as developers, it does present a difficulty. How do I develop something without breaking anything while I’m building it? I get stuck between a rock and a hard place. On one hand, the code I’m working on is in an unstable state, some of the unit tests don’t pass, and I’ve been working on said feature for about 2 days. I can’t check-in my code because it’s unstable, but I also don’t want to lose the history of what I’ve been working on.

Enter Git once again. Remember what I said earlier about Git having both cryptographic authentication of history and that it snapshots the entire state of the tree? Well, here’s what this means: I can use Git on top of an existing version control system like SVN or TFS, without affecting what they do for source control, and still keep a history of changes, and backups of those changes either somewhere on my computer, an external drive, or a network drive. And that cryptographic history thing? All it means is that it hashes the contents of the file. It could care less if TFS changes the read only state or about checkouts or checkins, because git is tracking it by what has actually changed.

Last Remarks

Hopefully, this enough to get you on your feet and get you started, if you want to get started and play with it (which I’d highly recommend) this post I’ve written earlier should get you up and running with Git, Git Extensions (a UI layer for Git) and Visual Studio.

Up and Running with GIT and Visual Studio

Another resource I’d highly recommend is ProGit, a book that is also fully online that digs into the details of Git:


Speaker Slides and Info

Slides from the presentation: Undo Forever on Slideshare
Speaker Rate: Speaker Rate

SQL For XML fails with UNION or UNION ALL

I ran across this quirky feature in SQL server 2000 and up that I need to put somewhere in the off chance I need to work with it again. Its this feature called ‘for xml‘, and it essentially allows you to return the result of a query in xml format. Yay.

It works fine for the most part, there are plenty of examples out there about the different modes and syntax and how to make it work, but if you run into an error like this:

Msg 1086, Level 15, State 1, Line 8
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

Chances are, your wrote some SQL that looked similar to this:

select top 2 
	CompanyId as "@Id",
	CompanyName as "@Name",
	select * from 
			(select top 5 * from Customer where CustomerType = 'new'
				for xml path('Customer'), root('NewCustomers'), TYPE)
			(select top 5 * from Customer where CustomerType = 'old'
				for xml path('Customer'), root('OldCustomers'), TYPE)
	for xml path(''), TYPE
from Company
for xml path('Company'), root('CompanyData'), TYPE

In an attempt to produce XML that looked like this:

	<Company Name="ACME Soft" Id="25">
			<Customer&g	t;
	<Company Name="Example Ware" Id="13">

but unfortunately, no matter what way you slice and dice it you’ll continue to get errors and warnings and nesting won’t work correctly and adding parenthesizes (Which one would think should NOT affect the order of execution for a query) causes query execution failures.

Long and the short of it is, when your using the for XML, you don’t need to union the results of the two sub queries, for XML will figure it out, so try and rewrite your query this way without the UNION ALL:

select top 2 
	CompanyId as "@Id",
	CompanyName as "@Name",
		(select top 5 * from Customer where CustomerType = 'new'
				for xml path('Customer'), root('NewCustomers'), TYPE)
		(select top 5 * from Customer where CustomerType = 'old'
				for xml path('Customer'), root('OldCustomers'), TYPE)

		for xml path(''), TYPE)
from Company
for xml path('Company'), root('CompanyData'), TYPE