SQL to XML

Recently I had to build some XML files for importing into a Desire2Learn instance. They were nice enough to provide an XSD and a little bit of sample XML, but I didn’t want to actually build another console application to generate an XML file. I wanted to just export data straight from my SQL server. Enter FOR XML. There is a lot of documentation available for this feature, but a lot of it assumed that your column names matched very well with the shape of the XML you wanted to make. Thankfully FOR XML is actually quite powerful in letting you designate what shape your XML looks like.

For example, an XML file for importing might look like this:

<enterprise>
 <group recstatus="2">
   <sourcedid>
     <id>MATH_101</id>
   </sourcedid>
   <grouptype>
     <typevalue level="4">Course Template</typevalue>
   </grouptype>
   <description>
     <short>MATH_101</short>
     <long>Basic Math</long>
   </description>
   <relationship relation="1">
     <sourcedid>
       <id>MATH</id>
     </sourcedid>
     <label>A parent of MATH_101 is the Math Department</label>
   </relationship>
   <extension>
     <path>MATH/templates/MATH_101/</path>
   </extension>
 </group>
</enterprise>

You might have the data in your SQL database that could be queried simply like this:

SELECT Template, CourseDescription, Department
FROM Courses

To get from the “Normal” SQL to the XML output, you use FOR XML, and aliasing that looks like XPATH notation. Prefixing the last token in the alias turns that value into an attribute of the previously named tag.

SELECT '2' AS '@recstatus',
Template AS 'sourcedid/id',
'4' AS 'grouptype/typevalue/@level',
'Course Template' AS 'grouptype/typevalue',
Template AS 'description/short',
CourseDescription AS 'description/long',
1 AS 'relationship/@relation',
Department AS 'relationship/sourcedid/id',
'A parent of ' + Template + ' is the ' + Department + ' Department' AS 'relationship/label',
Department + '/templates/' + Template + '/' AS 'extension/path'
FROM Courses
FOR XML PATH('group'), ROOT('enterprise')

Easy!

jQuery 2.0 and NuGet

Recently jQuery 2.0 was released to the world. Yay! It has many breaking changes. Boo! But they are going to keep the 1.x branch updated for the foreseeable future. Yay! NuGet however, does not currently have UI to selectively update only using the 1.x branch of jQuery. Boo!

Enter NuGet version contraints. Simply open up your packages.config, and find the following line:

<package id="jQuery" version="1.9.1" targetFramework="net45" />

Your targetFramework attribute may be different, but that doesn’t matter. What you need to do is edit that line to add the allowedVersions parameter:

<package id="jQuery" version="1.9.1" targetFramework="net45" allowedVersions="[1,2)" />

This tells NuGet that you want to constrain the jQuery package to versions 1 <= x < 2. In fact, if you use Update-Package, you will get a friendly line in the package manager console that reads:

Applying constraint 'jQuery (≥ 1.0 && < 2.0)' defined in packages.config.

Yay! Hopefully the UI for NuGet is updated soon to better support parallel release branches.

Steam and OpenID and MVC

Steam actually offers some options to people who want to build applications and services using their data. One of these options is that they offer Steam as an OpenID provider. MVC 4 actually makes this very easy to consume. I only had to add the following to my AuthConfig.cs to enable it:

using DotNetOpenAuth.AspNet.Clients;
// ... SNIP ....
public static void RegisterAuth() {
    OAuthWebSecurity.RegisterClient(
        new OpenIdClient("Steam", "http://steamcommunity.com/openid"),
        "Steam", null
    )
// ... SNIP ...
}

Magic!