Getting data out of XML as table in T-SQL

I had XML output of a table in XML variable and wanted to get the same table back again. You would find lots of questions and articles on this subject that all assume that you already know the column names, path etc to get the column values.

But what if you don't? There is hardly any reference of examples. And MS Documentation is just too much text without any value.

So here is a simple example of doing that.

declare @x xml
set @x = (select top 10 col1, col2, col3 From My.SampleTable for xml auto)
--PRINT convert(varchar(max), @x)






select t.c1.value('data(attribute::node()[1])', 'varchar(max)'), t.c1.value('data(attribute::node()[2])', 'varchar(max)')
from @x.nodes('child::node()') t(c1)

In above we don't have to know what the table name is and what the column names are. Here its assumed that the XML is a simple XML that has all nodes at the same level. Still what you have to do know is the number of columns that you want out of it. In above example, it has 3 columns in source table, but the result tries to fetch only two columns out of XML.

Following links were referred:
http://msdn.microsoft.com/en-us/library/ms190687.aspx
http://msdn.microsoft.com/en-us/library/ms177470.aspx
http://msdn.microsoft.com/en-us/library/ms191541.aspx

If you go through these MS document, you will have to read through too much text to find out what you need and their examples were not useful.

Visual Studio VS 2010 Crashes

Visual Studio VS 2010  Crashes

I experienced The Visual Studio 2010 crashes often today. Everytime I open a project, it was crashing. Searched the net but none of the solution really worked. I could open the VS 2010 but whenever I open a project, it was crashing.

Here are some solutions that may help:

1. Start the VS 2010 in administrator mode. Some recent changes or download may require it to install or update something and that may cause it to crash.

2. Check this lengthy list of q&a on MSN site (if you dont want to, this blog post tries to give the list of actions you can take)


http://social.msdn.microsoft.com/Forums/en-GB/vswpfdesigner/thread/2cfaf28f-80af-49ee-8408-07b4a8e0e964

3. Install latest WPF Toolkit

4. Install SP1 or whichever SP is latest for 2010 when you read this

5. This helped me - try uninstalling each Extension. In my case there were some Telerik Extensions that were causing this. Go to Tools > Extension Manager > and then uninstall the extensions by selecting them. Some of them may require you to uninstall from Windows Control Panel > Programs and Feature

6. Go to Start > MS VS 2010 > VS tools > Command Prompt

Go to folder C:\Program Files\MS VS 10> or whichever is your folder.

run devenv.exe /Log C:\yourlogfilename.txt

After the crash open the log file and see if there is anything that makes sense.

Pretty much Step 5 should help from above.

kendoUI - adding menu using web service

kendoUI is still in the beginning stages. Eventhough they have a commercial version there is quite a bit of functionalities needed before it can be fully used for a large application.


I am not sure what is the best practice for using each control and I would like to know this myself from @Telerik.  But here is an example of how to populate the kendoMenu dynamically using a web-service.


General approach is that each control/widget on the page should get the data from web-service. That way there is no server request. Now, our web-service can internally get the data from anywhere (database, xml file or any other appropriate way.) There will be a question about menu based on user-rights. But let's save that for later.


#1 - for now, let's assume that we have a web-service that has a function like following to return the menu details. 

    [WebService] //(Namespace = "http://WebService/")
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class WebService : System.Web.Services.WebService
    {
        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public List GetMenu()
        {
            List items = new List();
            items.Add(new MenuItem() { ParentTitle = "", Title = "Home", Link = "default.aspx" });
            items.Add(new MenuItem() { ParentTitle = "", Title = "Edit", Link = "edit.aspx" });
            items.Add(new MenuItem() { ParentTitle = "", Title = "View", Link = "view.aspx" });
            items.Add(new MenuItem() { ParentTitle = "Home", Title = "Login", Link = "view.aspx" });
            items.Add(new MenuItem() { ParentTitle = "Home", Title = "Profile", Link = "view.aspx" });
            items.Add(new MenuItem() { ParentTitle = "Home", Title = "Meetings", Link = "view.aspx" });
            items.Add(new MenuItem() { ParentTitle = "Home", Title = "Logout", Link = "view.aspx" });
            items.Add(new MenuItem() { ParentTitle = "Edit", Title = "Cut", Link = "view.aspx" });
            items.Add(new MenuItem() { ParentTitle = "Edit", Title = "Copy", Link = "view.aspx" });
            items.Add(new MenuItem() { ParentTitle = "Edit", Title = "Paste", Link = "view.aspx" });
            return items;
        }
} 


#2 - we can declare our MenuItem class having all possible properties that we want to make use of in the front-end. We may want to have some unique id, or type of menu etc. but below is a simpler form of such menu item class.
    public class MenuItem
    {
        public string ParentTitle { get; set; }
        public string Title { get; set; }
        public string Link { get; set; }
        public string IconUrl { get; set; }
    }

#3 - the html can be as simple as 

<body>
     <ul id="menu">
 
 </body>



#4 - let's declare a separate javascript file that can contain the javascript code for jQuery and the interface for the web-service. Here is a default ajaxSetup method that contains all default values - that way we don't have to specify it everytime we make a web-service call via jQuery. Please note the contentType - if this is missed then parser error will be raised by jQuery.


$.ajaxSetup({
    type: 'POST',
    contentType: "application/json; charset=utf-8",
    dataType: "json",
    data: "{}",
    success: function(msg) {
        console.log(msg.d);
    },
    error: function(xhr, desc, ex) {
        console.log(xhr.responseText);
        console.log(desc);
        console.log(ex);
    }
});


#5 - I owe following code to http://elegantcode.com/2011/01/26/basic-javascript-part-8-namespaces/ 
Its better to keep things in a namespace as a good practice.


function namespace(namespaceString) {
    var parts = namespaceString.split('.'),
        parent = window,
        currentPart = '';


    for (var i = 0, length = parts.length; i < length; i++) {
        currentPart = parts[i];
        parent[currentPart] = parent[currentPart] || {};
        parent = parent[currentPart];
    }


    return parent;
}


var myajax = namespace('myajax');


#6 - now its better to keep the web-service method calls in its own javascript class (singleton). That way we can call it easily w/o knowing about all the ajax overhead (of specifying url etc)


    
//Type.registerNamespace('myajax');
myajax.WebService = {
    Url: '/WebService.asmx/',


    _Ajax: function(url, callback) {
        $.ajax({
            url: url,
            success: callback
        });
    },


    GetMenu: function(callback) {
        var url = this.Url + 'GetMenu';
        this._Ajax(url, callback);
    }


};


You can expand above function to add new web-service methods when your service supports more methods. as you can see _Ajax function takes care of using jQuery internally to make the call and GetMenu function takes care of which Url should be used to call it. This is more like the code that is generated by .Net Script Manager for the web-service script references. 


#7 - now in your html following script is required to actually add the menu items.


    $(document).ready(function() {
        $("#menu").kendoMenu();


        myajax.WebService.GetMenu(function(menulist) {
            var menu = $("#menu").kendoMenu().data("kendoMenu");
            var parentItem = null;
            //jQuery.each(menulist.d, function(rec) {
            $(menulist.d).each(function(rec) {
                var parentItem = menu.element.children("li:contains('" + this.ParentTitle + "')");
                if (this.ParentTitle == '' || parentItem.length == 0) {
                    menu.append({ text: this.Title });
                }
                else
                    menu.append([{ text: this.Title}], parentItem);
            });
        });
    });


Here, we indicate that we want to convert

into kendoMenu. Then we call the web-service method GetMenu(). See the syntax of calling web-service method? Its 'myajax.WebService.GetMenu' - that is more elegant then trying to call $.ajax or $.post here.


the callback function receives the json list of MenuList class that was declared in the web-service. so callback method goes through each record of MenuList and appends an item using kendoMenu object's methods. It tries to find the parent using "title" of the menu item - you could change this to use any different method of finding the parent.


Notes:
• This is just a sample of how we can create menu based on web-service return values. I believe there should be better way. 
• Question that I would raise here in terms of better practice is - should the menu 'html' be already rendered when the page renders or should it be loaded using javascript as above?
• Telerik ASP.Net Rad Controls has a much richer objects for RadMenu and RadMenuItem to support a rich set of functionalities for images/checkboxes/finding menu item by text or parent etc. KendoUI still lacks all that 
• Here we have to look for parent using .element.children("li:contains") - this is kind of hodge podge of object oriented coding with html elements - and no serious developer would prefer that. kendoUI must support some methods like 'findMenuByText' to get the element we need. 
• Also append() should be allowed at menu-item level rather than at 'menu' level only. 

calling asp.net (asmx) web service from jquery .ajax()

There is lot of information out there on this very topic but I got errors while implementing it and finally fixed it. So I wanted to post my findings with example. 1) When you create .Net ASMX Web Service, it will look like following: 

 [WebService] //(Namespace = "http://WebService/") 
 [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] [System.ComponentModel.ToolboxItem(false)] 
[System.Web.Script.Services.ScriptService] 


 public class WebService : System.Web.Services.WebService 
 { 
 [WebMethod] 
  public string HelloWorld() 
 { 
 return "Hello World"; 
 }
}


Here the attribute in bold is very important. That indicates that this service can be called from the Javascript+ajax methods. 


You may see [ScriptMethod(ResponseFormat = ResponseFormat.Json)] attribute used for the Web-methods but this may not be necessary. At least when I ran the example I could run it successfully with and and without this attribute. Because the response format of the method will be determined based on the content-type used by the caller. Hence its important to set the 'content-type' from caller as below:


        $.ajax({
        type: 'POST',
            contentType: "application/json; charset=utf-8",
            url: 'http://localhost:1197/WebService.asmx/HelloWorld',
            dataType: "json",
            data: "{}",
            success: function(msg) {
                console.log(msg.d);
            },
            error: function(xhr, desc, ex) {
                console.log(xhr.responseText);
                console.log(desc);
                console.log(ex);
            }
        });

I was trying above example w/o 'contentType' and error function was called with 'parser error'. So having contentType in above is really important. 

Passing "{}" for data is not that important when your functions do not have any parameters. I could successfully call function w/o it, but if you get errors, you can try that.

Same for dataType: "json" - I commented it out and returned value was still interpreted as "json". I believe - again this is due to the contentType.

$telerik not found in IIS 7 windows server 2008

Of course, I don't understand Spanish. But following blog was helpful when I got the error installing a asp.net-telerik-web app on IIS 7, Windows Server 2008. The App was running fine in IIS 6, windows server 2003. When installed on 2008, on the client side, got javascript error that '$telerik' not defined, Type not defined etc.

http://msmvps.com/blogs/lalfaro/archive/2009/12/08/error-telerik-is-undefined-bajo-windows-2008-server-amp-iis-7.aspx

From above blog, I found that the entry under the system.webserver/handlers need preCondition="integratedMode,runtimeVersion2.0" for the Telerik entry. Once I added that the application ran fine.

Hotmail is painful

I still keep using Hotmail. But its very painful to use it:

- I wanted to delete some old email addresses - but in the contact list it will only show the name and not the email address. Hence you have to open the contact and then delete it; cant delete them from the list itself.

- when you type any email address in the [To] or [CC,BCC] boxes they are converted into some box with [x] next to it. You can't copy them any more and move it to one to another places like To->CC->Bcc etc. Also you cant simply use keyboard and update any addresses you have to use mouse to edit it.

Maps of War - Progress of Religion and War over time

Maps of War - Progress of Religion and War over time... very interesting flash graphics...
http://mapsofwar.com/images/Religion.swf