Exporting XML From MS SQL Server


This is going to be a short post on how to query an MS SQL Server database and produce XML output.

The Query

There are two formats that you can use when you want to convert a query result into XML, Elements format and Properties Format.  The following query will select from a table named “product” and put the result set into elements:

SELECT * FROM product FOR XML RAW ('product'), ROOT('data'), ELEMENTS

The results window will look something like this:

Click on the url link and the output should look like this:

    <Name>Matchbox Car</Name>
    <Name>Baby Rattle</Name>
    <Name>Lego Bricks</Name>

If you want to flatten out your XML, you can use this syntax:

SELECT * FROM product FOR XML RAW ('product')

Which will produce this output:

<product ProductId=”1ProductType=”1Name=”Matchbox Carstore=”1” />
<product ProductId=”2ProductType=”1Name=”Baby Rattlestore=”1” />
<product ProductId=”3ProductType=”1Name=”Lego Bricksstore=”1” />
<product ProductId=”4ProductType=”2Name=”Steakstore=”1” />

As you can see by the last example, you can leave the “Root” keyword off the query if you don’t want to the root data tags added.

Producing JSON

If you need to produce JSON, you will need to do it using another tool.  Use the For XML RAW command above, including the root tag (the output must be well-formed.  Then go to this site (or any other free xml to json converter website): thomasfrank.se XML to JSON – a converter

Paste your xml into the window and hit the convert button.


MVC – List of Check Boxes


In this post I’m going to show how to handle a list of check boxes in an MVC application.

The Interface

I’ve kept this example as simple as possible to point out the tricky spots when attempting to deal with check boxes.  For those of you who have worked with HTML and the POST form submit, you’ll know that check boxes are annoying.  The basic problem with a check box is that only the check boxes that are checked will appear in the list of submitted objects.  If you have thousands of check boxes on your screen, then that would be a more efficient method of submitting your data.  Leaving the server code to sort out what changed since the page was rendered.  However, if you have 100 or fewer check boxes, it makes more sense to just submit all the check boxes back with a flag to indicate if it was checked or not.

The following is a screenshot of the screen I’m going to use.  The check box list is dynamic and is determined by a data source.  In this example, the list of names on the screen will be populated manually (i.e. hard-coded).  In a real application you’ll need to determine what data will be used in your check list and query the data using an ORM or ADO, etc.

The Model

 Let’s start with the model.  First I created an object to represent one person record, or in this case, one check box of information:

public class PersonModel
    public string Name { get; set; }
    public bool Checked { get; set; }

This object is pretty easy to figure out.  There is a name that forms the title next to the check box.  Your data might consist of a first and last name, in which you can concatenate them using Razor on your view.  The checked boolean value is used to represent the fact that the check box is checked or not.

Then I created another object to represent the entire view consisting of a list of personnel, or a list of check boxes:

public class HomeViewModel
    public List<PersonModel> Persons { get; set; }

That concludes the model itself.

The Default Controller Method

The controller consists of two methods.  The first method sets up the data and sends it to the view:

public ActionResult Index()
    var myHomeViewModel = new HomeViewModel();

    myHomeViewModel.Persons = new List<PersonModel>();
    myHomeViewModel.Persons.Add(new PersonModel { Name = “Frank“, Checked = false });
    myHomeViewModel.Persons.Add(new PersonModel { Name = “Joe“, Checked = false });
    myHomeViewModel.Persons.Add(new PersonModel { Name = “Sue“, Checked = false });
    myHomeViewModel.Persons.Add(new PersonModel { Name = “Mike“, Checked = false });

    return View(myHomeViewModel);

This is all very basic stuff.  Just initialize a list of personnel (called myHomeViewModel) and populate it with 4 items.  Then make sure you pass the list to the view.

I’ll show how the submit controller works after I describe the view.

The View

In order to use your model data, you need to declare the model at the top of the view:

@model MVCMultiCheckBox.Models.HomeViewModel

Then the real work occurs inside the form tags:

@using (Html.BeginForm(“Submit“, “Home“))
    for (int i = 0; i < Model.Persons.Count; i++)
            @Html.CheckBoxFor(m => m.Persons[i].Checked, new { id = “cbPerson_” + i })
            @Html.DisplayFor(m => m.Persons[i].Name)
        </div><br />
        @Html.HiddenFor(m => m.Persons[i].Name);

    <div><input type=”submitvalue=”Submitname=”Submitclass=”btn btn-default” /></div>

The controller’s name is “Home” and the controller post method will be named “Submit”.  Keep that in mind when you’re trying to figure out why your submit doesn’t do anything.  Next, is the for loop.  That will iterate through the list of check boxes defined in the model.  Inside the loop are three (3) very important items: The check box, the displayed name and a hidden field.  The check box and name are obvious, but the hidden field is used to make sure that something is submitted when the check box is not checked.  The name of the hidden field will be the same name as the check box.  If a check box is set, then it will be returned, if not, then the hidden field will be returned which will be set to false (all hidden fields have a value of false).  Run the program, right-click on the web page and view the source to see what is rendered to the browser.

The Http POST Controller

Finally, the post controller looks like this:

public ActionResult Submit(HomeViewModel result)
    return View(“Index“, result);

Which does nothing with the result set.  You’ll need to put a break-point on the “return view()” line and submit some data.  Then look at the values in side of “result”.  You should see your model updated with the correct true/false representing the check boxes that you checked on the interface.  Here’s the result variable in the watch window after checking joe and sue (with everything expanded):

The POST controller is where you’ll need to call a save method in your personnel business class and save the changed results back to the database.  If you’re attempting to reduce the amount of chatter to your database, you can setup a “PreviouslyChecked” flag for each record and then only save back records where the “Checked” is not equal to “PreviouslyChecked”.  Of course, there are many other possible ways to handle the updating of your database from the results returned from your view, but that is not for this blog post.

How to Get The Code

You can download the sample source from my GitHub account by clicking here.