T-SQL Tree Into XML

My previous post contained a link to a Microsoft article detailing how to get well structured XML directly from T-SQL. This post is about getting a tree of data from T-SQL into XML. FOR XML EXPLICIT cannot be used (without major headaches) but a simple recursive function and a SELECT can be used. Here's where I poached my solution from:

• http://stackoverflow.com/questions/2409228/generate-structured-xml-document-from-hierarchical-table-data-t-sql/2425180#2425180

In short, my code is something like this (generic code for a wider audience):

CREATE function [dbo].[SelectCategoryChild](@categoryid as int)
returns xml
begin
    return (
        select
            categoryid as "@id",
            parentcategoryid as "@parentcategoryid",
            title as "@title",
            dbo.SelectCategoryChild(categoryid)
        from category
        where parentcategoryid = @categoryid
        for xml path('category'), type
    )
end
GO
SELECT
    categoryid AS "@categoryid",
    '' AS "@parentcategoryid",
    title as "@title",
    dbo.SelectCategoryChild(categoryid)
FROM category
WHERE parentcategoryid is null and siteid = 1
FOR XML PATH ('category'), root('categories')

The code above works for a single table created from the statement (references and indices omitted for clarity):

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Title] [varchar](150)
)

CategoryID is the primary key,

ParentCategoryID references the CategoryID and provides theone-to-many tree structure, a NULL value indicates the node is a root node (top-level) in the tree,

Title is simply some data (in this case the name) of the node.

LINQ2SQL Transaction

A good thread to read on LINQ to SQL transactions, centred on commits and rollbacks:

• http://stackoverflow.com/questions/542525/transactionscope-vs-transaction-in-linq2sql

Another Problem Solved By Someone Better Than Me

Ok, this is one of those woods-for-the-trees situations. Basically, I had been mucking about with custom model binders:

• http://stackoverflow.com/questions/2229826/mvc-generates-two-input-fields-for-the-same-bool-property-how-do-i-stop-it/2230083#2230083

And had added a class attribute to my model class to handle the post back binding of the model. This worked fine. The following, however, I forgot about this and was trying to use the default server-side validation.

Problem: The server-side validation was not working.

Solution: Remove the custom model binder.

Reason: It was returning just an empty model object every time.

This solved a host of problems, in fact.

The EditorTemplates Directory

Having been trying to get a model property to render the appropriate editor in MVC, recently, I was having some trouble getting the editor - provided by a very talented colleague - to render correctly, or, in fact, at all.

Not having been introduced to the structure of the shared directory (it doesn't seem to be mentioned in the http://www.asp.net/learn/mvc/ pages) there was an issue with understanding how the Html.EditorFor method would provide my custom editor.

This was solved when I came across David Hayden's blog page:

• http://davidhayden.com/blog/dave/archive/2009/08/21/HtmlEditorForScaffoldColumnAttribute.aspx

And so I discovered that the control should be placed in the /Shared/EditorTemplates directory. Low and behold, the code you can find on this page worked:

• http://stackoverflow.com/questions/2222743/how-to-pass-an-enum-to-html-radiobuttonfor-to-get-a-list-of-radio-buttons-in-mvc/2230595#2230595

How to be productive...

A good question with some nice, simple, straightforward answers, which don't just apply to developers: http://stackoverflow.com/questions/645237/as-a-programmer-what-single-discovery-has-given-you-the-greatest-boost-in-product