Three steps to use jQuery UI in ASP.NET MVC 5


Many developers struggle to work with jQuery UI in an ASP.NET MVC application. In this post, I will show you three steps required to start working with jQuery UI in an ASP.NET MVC application. At the end of the post we will take a look at working with the autocomplete widget.
The following three steps will let you work with jQuery UI in an ASP.NET MVC application:
Step 1: Add the jQuery UI Reference
Add the jQuery UI reference into the project using the NuGet manager. Once this is done, you should find the reference added in the Content folder and the Scripts folder.


Step 2: Bundle the required files
Open the BundleConfig.cs file. In this file add two entries, one for the jQuery UI scripts and other for jQuery UI CSS.
Add the script entry as follows:

Next add the CSS files for jQueryUI widgets.  CSS for all the widgets can be bundled like this:

For the purpose of this example, let’s say you are only working with the autocomplete widget. In this case, you would only bundle the core.css and autocomplete.css as shown below:

Step 3: Refer to the Bundles
Once the bundles for jQuery UI have been created, you need to add them to the layout file. That can be done as follows:

By default you will find the jQuery bundle at the end of the layout file. To work with jQuery UI widgets, you should move the jQuery bundle to the top of the file and also include the bundles for jQuery UI CSS and Scripts.
You have now completed the three steps necessary to work with jQueryUI in an ASP.NET MVC application.
Use jQueryUI Widgets 
Now let’s look at the autocomplete widget in action.  I have created a controller for returning JSON data as follows:

We will now bind the returned JSON from the GetMP() action to the autocomplete widget. On the razor view, you can create an autocomplete widget like this:

Make sure the view is using the layout file in which you added the reference of the bundles.
We’ve seen in this post that working with jQuery UI widgets is as simple as following three simple steps:

  1. Add the  jQuery UI reference
  2. Bundle the required files
  3. Refer to the bundles

The autocomplete widget is also a helpful tool when working with jQuery UI bundles.
I hope this post was useful. Happy coding.

How to add jQuery UI to your ASP.NET MVC App in 3 steps


This is a quick demo on how to add jQuery UI to your MVC project in Visual Studio 2015 but the process applies to earlier versions as well. For this example, I’ll use the accordion widget.

This is how the page looks before applying the accordion widget…


and after…

img-11The index.cshtml markup:

    ViewBag.Title = "Home Page";
<div id="accordion" class="prettyprint">
    <h3>Section 1</h3>
            Mauris mauris ante, blandit et, ultrices a, suscipit eget, quam. Integer
            ut neque. Vivamus nisi metus, molestie vel, gravida in, condimentum sit
            amet, nunc. Nam a nibh. Donec suscipit eros. Nam mi. Proin viverra leo ut
            odio. Curabitur malesuada. Vestibulum a velit eu ante scelerisque vulputate.
    <h3>Section 2</h3>
            Sed non urna. Donec et ante. Phasellus eu ligula. Vestibulum sit amet
            purus. Vivamus hendrerit, dolor at aliquet laoreet, mauris turpis porttitor
            velit, faucibus interdum tellus libero ac justo. Vivamus non quam. In
            suscipit faucibus urna.
    <h3>Section 3</h3>
            Nam enim risus, molestie et, porta ac, aliquam ac, risus. Quisque lobortis.
            Phasellus pellentesque purus in massa. Aenean in pede. Phasellus ac libero
            ac tellus pellentesque semper. Sed ac felis. Sed commodo, magna quis
            lacinia ornare, quam ante aliquam nisi, eu iaculis leo purus venenatis dui.
            <li>List item one</li>
            <li>List item two</li>
            <li>List item three</li>
    <h3>Section 4</h3>
            Cras dictum. Pellentesque habitant morbi tristique senectus et netus
            et malesuada fames ac turpis egestas. Vestibulum ante ipsum primis in
            faucibus orci luctus et ultrices posuere cubilia Curae; Aenean lacinia
            mauris vel est.
            Suspendisse eu nisl. Nullam ut libero. Integer dignissim consequat lectus.
            Class aptent taciti sociosqu ad litora torquent per conubia nostra, per
            inceptos himenaeos.
@section scripts

Notice at the bottom the @scripts section. This is defined in your _Layout.cshtml view. There you will find the @RenderSection method being called as you can see below:


    @RenderSection("scripts", required: false)

1) Install jQuery.UI.Combined

jQuery is already included in your project but not jQuery-UI. Install jQuery.UI.Combined with the Nuget Package Manager. This package includes all of the jQuery-UI widgets and animations.


A quicker way of installing it is by typing the following command in the Package Manager Console:

PM> Install-Package jQuery.UI.Combined

2) Add the jQuery-UI script and style bundles

Add the script and style bundles to your BundleConfig.cs file in the App_Start folder.

using System.Web;
using System.Web.Optimization;
namespace jQueryDemo
    public class BundleConfig
        public static void RegisterBundles(BundleCollection bundles)
            bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
            bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
            // Add the jquery-ui script bundle
            bundles.Add(new ScriptBundle("~/bundles/jquery-ui").Include(
            // Add the jquery-ui css bundle
            bundles.Add(new StyleBundle("~/Content/themes/base/css").Include(
            bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
            bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
            bundles.Add(new StyleBundle("~/Content/css").Include(

3) Call the Script and Style Render Methods in the Layout View

Finally, call the @Scripts.Render() and @Styles.Render() methods in your _Layout.cshtml after the jQuery render method is called.

    <!-- Including the jquery-ui script and style bundles -->
    @RenderSection("scripts", required: false)

That’s it! Build your project, refresh the page, and enjoy the results. If you don’t include the installation, it’s basically a two-step process. Include the script and style bundles in your BundleConfig file and call the render methods from your layout view.

Applying a theme

If the base theme is too plain-looking you might want to go for one of the many different themes offered by jQuery-UI. Let’s go ahead an apply the Redmond theme to our project. Begin by installing jQuery.UI.Themes through the Nuget Package Manager. This package includes all of the jQuery themes in one download.


Once it’s installed check that the themes appear in your solution explorer in the Content > Themes folder.


Now you need to add the theme’s style bundle in your BundleConfig.cs file and remove the base theme. In our case, we’ll make sure the style bundle points to redmond/jquery-ui-redmond.css.

using System.Web;
using System.Web.Optimization;
namespace jQueryDemo
    public class BundleConfig
        public static void RegisterBundles(BundleCollection bundles)
            bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
            bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
            bundles.Add(new ScriptBundle("~/bundles/jquery-ui").Include(
            // Removing the base theme.
            // bundles.Add(new StyleBundle("~/Content/themes/base/css").Include(
            //            "~/Content/themes/base/all.css"));
            // Adding the 'redmond' style bundle.
            bundles.Add(new StyleBundle("~/Content/themes/redmond/css").Include(
            bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
            bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
            bundles.Add(new StyleBundle("~/Content/css").Include(

Finally, head to your _Layout.cshtml and change @Styles.Render(“~/Content/themes/base/css”) to @Styles.Render(“~/Content/themes/redmond/css”)

    <!-- Including the jquery-ui script and style bundles -->
    @RenderSection("scripts", required: false)

Rebuild the project and the theme should now be active when you launch the application.


View JSON in Internet Explorer 7-11


Hello guys,

you all know the problem, want to debug an application with IE and IE always wants to download the JSON file.

The other browsers like Chrome or Firefox are doing a better job here.

If you want that IE shows the JSON file without downloading it, here is a registry fix for it.

Create a new text document and insert this (Link:

Windows Registry Editor Version 5.00;
; Tell IE 7,8,9,10,11 to open JSON documents in the browser on Windows XP and later.
; 25336920-03F9-11cf-8FD0-00AA00686F13 is the CLSID for the "Browse in place" .
[HKEY_CLASSES_ROOT\MIME\Database\Content Type\application/json]

[HKEY_CLASSES_ROOT\MIME\Database\Content Type\text/json]

Now rename the document to {FileName}.reg and save.

Run it and confirm the dialog.

Now JSON files will be shown directly in IE, without downloading.

*Thanks to Coding 101 from for the Script

Debugging JavaScript Using Google Chrome


We are working with data and data are storing in JavaScript variables. We need to see what value is passed to the function and how the value is manipulated and what result the value is producing so we need to debug the JavaScript function to trace a variable value line by line. In the previous article I explained how to debug a JavaScript function using Visual Studio and Internet Explorer; that article is:

Debugging JavaScript Code Using VS and IE

So now we go through step-by-step to debug a JavaScript function using Google Chrome.

Create UI Design and Develop Functionality for Addition

We create a UI Design that takes two values and calculates the addition of these two values on a button click.

<html xmlns="">
<head runat="server">
    <script type ="text/javascript">
        function AddNumber()
            var result = document.getElementById("<%=lblResult.ClientID %>");
            var firstNumber = document.getElementById("<%=txtFirstNumber.ClientID %>").value;
            var secondNumber = document.getElementById("<%=txtSecondNumber.ClientID %>").value;
            var addition = parseInt( firstNumber) + parseInt(secondNumber);
            result.innerHTML = "Addition is :" + addition;
            return false;
    <form id="form1" runat="server">
    Enter First Number : <asp:TextBox ID="txtFirstNumber" runat="server"></asp:TextBox><br />
    Enter Second Number : <asp:TextBox ID="txtSecondNumber" runat="server"></asp:TextBox><br />
    <asp:Button ID="btnAdd" runat="server" Text="Addition" OnClientClick="javascript:return AddNumber();" /><br/>
    <asp:Label ID="lblResult" runat="server"></asp:Label>

Value Input Window


Add Debugger in the JavaScript function so the cursor can hit here whenever the function is called.


Setting and Debug In Google Chrome

Go to “Tools” then click on “JavaScript console”.


Run the application and insert values into the input fields then click on the “Addition” button. The cursor will then reach the debugger in the source tab under the JavaScript console window.


In this function we declare several variables and on starting, these variables are #ff0000; check the Scope Variables panel in the Right Pane.


Click on F10 and move forward line by line, then check the value of the variables using a mouse move on that variable.


We can also check data in the scope variables window.


We can also check for an error in the JavaScript code. For example we don’t define a variable and use that variable. I have a variable firstNumber and use the myfirstNumber variable to parse in an int type but instead firstNumber is undefined.


We can insert a breakpoint by double-clicking on line number or using right-click of the mouse, then click on “Add breakpoint item”. When we use a breakpoint we don’t need to define the debugger in the JavaScript function and the cursor will hit the breakpoint directly.


We have the Breakpoints window where the line with the breakpoint checkbox checked will be shown.


JSON Data (SQL Server)


JSON is a popular textual data format used for exchanging data in modern web and mobile applications. JSON is also used for storing unstructured data in log files or NoSQL databases like Microsoft Azure DocumentDB. Many REST web services return results formatted as JSON text or accept data formatted as JSON. For example, most Azure services such as Azure Search, Azure Storage, and Azure DocumentDb have REST endpoints that return or consume JSON. JSON is also the main format for exchanging data between web pages and web servers using AJAX calls.

Here’s an example of JSON text:

    "name": "John",
    "skills": ["SQL", "C#", "Azure"]
}, {
    "name": "Jane",
    "surname": "Doe"

SQL Server provides built-in functions and operators that let you do the following things with JSON text.

  • Parse JSON text and read or modify values.
  • Transform arrays of JSON objects into table format.
  • Run any Transact-SQL query on the converted JSON objects.
  • Format the results of Transact-SQL queries in JSON format.

    Overview of built-in JSON support

Key JSON capabilities of SQL Server

Here’s more info about the key capabilities that SQL Server provides with its built-in JSON support.

Extract values from JSON text and use them in queries

If you have JSON text that’s stored in database tables, you can use built-in functions to read or modify values in the JSON text.

  • Use the JSON_VALUE function to extract a scalar value from a JSON string.
  • Use JSON_QUERY to extract an object or an array from a JSON string.
  • Use the ISJSON function to test whether a string contains valid JSON.
  • Use the JSON_MODIFY function to change a value in a JSON string.


In the following example, the query uses both relational and JSON data (stored in the jsonCol column) from a table:

SELECT Name,Surname,
 JSON_VALUE(jsonCol,'$.info.address.PostCode') AS PostCode,
 JSON_VALUE(jsonCol,'$.info.address."Address Line 1"')+' '
  +JSON_VALUE(jsonCol,'$.info.address."Address Line 2"') AS Address,
 JSON_QUERY(jsonCol,'$.info.skills') AS Skills
FROM PeopleCollection
WHERE ISJSON(jsonCol)&gt;0
 AND JSON_VALUE(jsonCol,'$.info.address.Town')='Belgrade'
 AND Status='Active'
ORDER BY JSON_VALUE(jsonCol,'$.info.address.PostCode')

Applications and tools see no difference between the values taken from scalar table columns and the values taken from JSON columns. You can use values from JSON text in any part of a Transact-SQL query (including WHERE, ORDER BY, or GROUP BY clauses, window aggregates, and so on). JSON functions use JavaScript-like syntax for referencing values inside JSON text.

For more info, see Validate, Query, and Change JSON Data with Built-in Functions (SQL Server), JSON_VALUE (Transact-SQL), and JSON_QUERY (Transact-SQL).

Change JSON values

If you have to modify parts of JSON text, you can use the JSON_MODIFY function to update the value of a property in a JSON string and return the updated JSON string. The following example updates the value of a property in a variable that contains JSON.


SET @jsonInfo=JSON_MODIFY(@jsonInfo,'$.info.address[0].town','London') 

Convert JSON collections to a rowset

You don’t need a custom query language to query JSON in SQL Server. To query JSON data, you can use standard T-SQL. If you have to create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more info, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).

The following example calls OPENJSON and transforms the array of objects stored in the @json variable to a rowset that can be queried with a standard SQL SELECT statement:

SET @json =  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" }  

  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob')  


id firstName lastName age dateOfBirth
2 John Smith 25
5 Jane Smith 2005-11-04T12:00:00

OPENJSON transforms the array of JSON objects into a table in which each object is represented as one row, and key/value pairs are returned as cells. The output observes the following rules.

  • OPENJSON converts JSON values to the types specified in the WITH clause.
  • OPENJSON can handle both flat key/value pairs and nested, hierarchically organized objects.
  • You don’t have to return all the fields contained in the JSON text.
  • OPENJSON returns NULL values if JSON values don’t exist.
  • You can optionally specify a path after the type specification to reference a nested property or to reference a property by a different name.
  • The optional strict prefix in the path specifies that values for the specified properties must exist in the JSON text.

For more info, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server) and OPENJSON (Transact-SQL).

Convert SQL Server data to JSON or export JSON

Format SQL Server data or the results of SQL queries as JSON by adding the FOR JSON clause to a SELECT statement. Use FOR JSON to delegate the formatting of JSON output from your client applications to SQL Server. For more info, see Format Query Results as JSON with FOR JSON (SQL Server).

The following example uses PATH mode with the FOR JSON clause.

SELECT id, firstName AS "", lastName AS "info.surname", age, dateOfBirth as dob  
FROM People  

The FOR JSON clause formats SQL results as JSON text that can be provided to any app that understands JSON. The PATH option uses dot-separated aliases in the SELECT clause to nest objects in the query results.


    "id": 2,
    "info": {
        "name": "John",
        "surname": "Smith"
    "age": 25
}, {
    "id": 5,
    "info": {
        "name": "Jane",
        "surname": "Smith"
    "dob": "2005-11-04T12:00:00"

For more info, see Format Query Results as JSON with FOR JSON (SQL Server) and FOR Clause (Transact-SQL).

Combine relational and JSON data

SQL Server provides a hybrid model for storing and processing both relational and JSON data using standard Transact-SQL language. You can organize collections of your JSON documents in tables, establish relationships between them, combine strongly-typed scalar columns stored in tables with flexible key/value pairs stored in JSON columns, and query both scalar and JSON values in one or more tables using full Transact-SQL.

JSON text is typically stored in varchar or nvarchar columns and is indexed as plain text. Any SQL Server feature or component that supports text supports JSON, so there are almost no constraints on interaction between JSON and other SQL Server features. You can store JSON in In-memory or Temporal tables, you can apply Row-Level Security predicates on JSON text, and so on.

If you have pure JSON workloads where you want to use a query language that’s customized for the processing of JSON documents, consider Microsoft Azure DocumentDB.

Here are some use cases that show how you can use the built-in JSON support in SQL Server.

Return data from a SQL Server table formatted as JSON

If you have a web service that takes data from the database layer and returns it in JSON format, or JavaScript frameworks or libraries that accept data formatted as JSON, you can format JSON output directly in a SQL query. Instead of writing code or including a library to convert tabular query results and then serialize objects to JSON format, you can use FOR JSON to delegate the JSON formatting to SQL Server.

For example, you might want to generate JSON output that’s compliant with the OData specification. The web service expects a request and response in the following format.

  • Request: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName
  • Response: {"@odata.context":"$metadata#Products(ProductID,ProductName)/$entity","ProductID":1,"ProductName":"Chai"}

    This OData URL represents a request for the ProductID and ProductName columns for the product with id 1. You can use FOR JSON to format the output as expected in SQL Server.

SELECT '$metadata#Products(ProductID,ProductName)/$entity'
 AS '@odata.context',   
 ProductID, Name as ProductName   
FROM Production.Product  
WHERE ProductID = 1  

The output of this query is JSON text that’s fully compliant with OData spec. Formatting and escaping are handled by SQL Server. SQL Server can also format query results in any format such as OData JSON or GeoJSON – for more info, see Returning spatial data in GeoJSON format.

Analyze JSON data with SQL queries

If you have to filter or aggregate JSON data for reporting purposes, you can use OPENJSON to transform JSON to relational format. Then use standard Transact-SQL and built-in functions to prepare the reports.

SELECT Tab.Id, SalesOrderJsonData.Customer, SalesOrderJsonData.Date  
FROM   SalesOrderRecord AS Tab  
          CROSS APPLY  
     OPENJSON (Tab.json, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
  AS SalesOrderJsonData  
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'  
ORDER BY JSON_VALUE(Tab.json, '$.Group'), Tab.DateModified  

Both standard table columns and values from JSON text can be used in the same query. You can add indexes on the JSON_VALUE(Tab.json, '$.Status') expression to improve performance of query. For more info, see Index JSON data.

Import JSON data into SQL Server tables

If you have to load JSON data from an external service into SQL Server, you can use OPENJSON to import the data into SQL Server instead of parsing the data in the application layer.


SET @jsonVariable = N'[  
          "Order": {  
          "Item": {  
          "Order": {  
          "Item": {  

INSERT INTO SalesReport  
SELECT SalesOrderJsonData.*  
FROM OPENJSON (@jsonVariable, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
  AS SalesOrderJsonData;

The content of the JSON variable can be provided by an external REST service, sent as a parameter from a client-side JavaScript framework, or loaded from external files. You can easily insert, update or merge results from JSON text into a SQL Server table. For more info about this scenario, see the following blog posts.

Load JSON files into SQL Server

Information stored in files can be formatted as standard JSON or Line-Delimited JSON. SQL Server can import the contents of JSON files, parse it using the OPENJSON or JSON_VALUE functions, and load it into tables.

  • If your JSON documents are stored in local files, on shared network drives, or in Azure File Storage locations that can be accessed by SQL Server, you can use bulk import to load your JSON data into SQL Server. For more info about this scenario, see Importing JSON files into SQL Server using OPENROWSET (BULK).
  • If your line-delimited JSON files are stored in Azure Blob Storage or the Hadoop file system, you can use Polybase to load JSON text, parse it in Transact-SQL code, and load it into tables.

Test drive built-in JSON support

Test drive built-in JSON support with the AdventureWorks sample database. To get the AdventureWorks sample database, download at least the database file and the samples and scripts file from here. After you restore the sample database to an instance of SQL Server 2016, unzip the samples file and open the “JSON Sample Queries procedures views and indexes.sql” file from the JSON folder. Run the scripts in this file to reformat some existing data as JSON data, run sample queries and reports over the JSON data, index the JSON data, and import and export JSON.

Here’s what you can do with the scripts included in the file.

  1. Denormalize the existing schema to create columns of JSON data.
    1. Store information from SalesReasons, SalesOrderDetails, SalesPerson, Customer, and other tables that contain information related to sales order into JSON columns in the SalesOrder_json table.
    2. Store information from EmailAddresses/PersonPhone tables into the Person_json table as arrays of JSON objects.
  2. Create procedures and views that query JSON data.
  3. Index JSON data – create indexes on JSON properties and full-text indexes.
  4. Import and export JSON – create and run procedures that export the content of the Person and the SalesOrder tables as JSON results, and import and update the Person and the SalesOrder tables using JSON input.
  5. Run query examples – run some queries that call the stored procedures and views created in steps 2 and 4.
  6. Clean up scripts – don’t run this part if you want to keep the stored procedures and views created in steps 2 and 4.

Learn more about built-in JSON support

Topics in this section

Format Query Results as JSON with FOR JSON (SQL Server)
Use the FOR JSON clause to delegate the formatting of JSON output from your client applications to SQL Server.

Convert JSON Data to Rows and Columns with OPENJSON (SQL Server)
Use OPENJSON to import JSON data into SQL Server, or to convert JSON data into relational format for an app or service that can’t currently consume JSON directly, such as SQL Server Integration Services.

Validate, Query, and Change JSON Data with Built-in Functions (SQL Server)
Use these built-in functions to validate JSON text and to extract a scalar value, an object, or an array.

JSON Path Expressions (SQL Server)
Use a path expression to specify the JSON text that you want to use.

Index JSON data
Use computed columns to create collation-aware indexes over properties in JSON documents.

Solve common issues with JSON in SQL Server
Find answers to some common questions about the built-in JSON support in SQL Server.

Microsoft blog posts

Reference topics

Consuming JSON Strings in SQL Server


Consuming JSON Strings in SQL Server

It has always seemed strange to Phil that SQL Server has such complete support for XML, yet is completely devoid of any support for JSON. In the end, he was forced, by a website project, into doing something about it. The result is this article, an iconoclastic romp around the representation of hierarchical structures, and some code to get you started.

Updated 2nd May 2013
Updated 8th  May 2014

“The best thing about XML is what it shares with JSON, being human readable. That turns out to be important, not because people should be reading it, because we shouldn’t, but because it avoids interoperability problems caused by fussy binary encoding issues.


Beyond that, there is not much to like. It is not very good as a data format. And it is not very good as a document format. If it were a good document format, then wikis would use it.”

Doug Crockford March 2010

This article describes a TSQL JSON parser and its evil twin, a JSON outputter, and provides the source. It is also designed to illustrate a number of string manipulation techniques in TSQL. With it you can do things like this to extract the data from a JSON document:

And get:


…or you can do the round trip:

To get:


TSQL isn’t really designed for doing complex string parsing, particularly where strings represent nested data structures such as XML, JSON, YAML, or XHTML.

You can do it but it is not a pretty sight; but why would you ever want to do it anyway? Surely, if anything was meant for the ‘application layer’ in C# or, then this is it. ‘Oh yes’, will chime in the application thought police, ‘this is far better done in the application or with a CLR.’ Not necessarily.

Sometimes, you just need to do something inappropriate in TSQL.

There are a whole lot of reasons why this might happen to you. It could be that your DBA doesn’t allow a CLR, for example, or you lack the necessary skills with procedural code. Sometimes, there isn’t any application, or you want to run code unobtrusively across databases or servers.

I needed to interpret or ‘shred’ JSON data. JSON is one of the most popular lightweight markup languages, and is probably the best choice for transfer of object data from a web page. It is, in fact, executable JavaScript that is very quick to code in the browser in order to dump the contents of a JavaScript object, and is lightning-fast to populate the browser object from the database since you are passing it executable code (you need to parse it first for security reasons – passing executable code around is potentially very risky). AJAX can use JSON rather than XML so you have an opportunity to have a much simpler route for data between database and browser, with less opportunity for error.

The conventional way of dealing with data like this is to let a separate business layer parse a JSON ‘document’ into some tree structure and then update the database by making a series of calls to it. This is fine, but can get more complicated if you need to ensure that the updates to the database are wrapped into one transaction so that if anything goes wrong, then the whole operation can be rolled back. This is why a CLR or TSQL approach has advantages.

“Sometimes, you just
need to do something
inappropriate in TSQL…”

I wrote the parser as a prototype because it was the quickest way to determine what was involved in the process, so I could then re-write something as a CLR in a .NET language.  It takes a JSON string and produces a result in the form of an adjacency list representation of that hierarchy. In the end, the code did what I wanted with adequate performance (It reads a json file of  540 name\value pairs and creates the SQL  hierarchy table  in 4 seconds) so I didn’t bother with the added complexity of maintaining a CLR routine. In order to test more thoroughly what I’d done, I wrote a JSON generator that used the same Adjacency list, so you can now import and export data via JSON!

These markup languages such as JSON and XML all represent object data as hierarchies. Although it looks very different to the entity-relational model, it isn’t. It is rather more a different perspective on the same model. The first trick is to represent it as a Adjacency list hierarchy in a table, and then use the contents of this table to update the database. This Adjacency list is really the Database equivalent of any of the nested data structures that are used for the interchange of serialized information with the application, and can be used to create XML, OSX Property lists, Python nested structures or YAML as easily as JSON.

Adjacency list tables have the same structure whatever the data in them. This means that you can define a single Table-Valued  Type and pass data structures around between stored procedures. However, they are best held at arms-length from the data, since they are not relational tables, but something more like the dreaded EAV (Entity-Attribute-Value) tables. Converting the data from its Hierarchical table form will be different for each application, but is easy with a CTE. You can, alternatively, convert the hierarchical table into XML and interrogate that with XQuery.

JSON format.

JSON is designed to be as lightweight as possible and so it has only two structures. The first, delimited by curly brackets, is a collection of name/value pairs, separated by commas. The name is followed by a colon. This structure is generally implemented in the application-level as an object, record, struct, dictionary, hash table, keyed list, or associative array. The other structure is an ordered list of values, separated by commas. This is usually manifested as an array, vector, list, or sequence.

“Using recursion in TSQL is
like Sumo Wrestlers doing Ballet.
It is possible but not pretty.”

The first snag for TSQL is that the curly or square brackets are not ‘escaped’ within a string, so that there is no way of shredding a JSON ‘document’ simply. It is difficult to  differentiate a bracket used as the delimiter of an array or structure, and one that is within a string. Also, interpreting a string into a SQL String isn’t entirely straightforward since hex codes can be embedded anywhere to represent complex Unicode characters, and all the old C-style escaped characters are used. The second complication is that, unlike YAML, the datatypes of values can’t be explicitly declared. You have to sniff them out from applying the rules from the JSON Specification.

Obviously, structures can be embedded in structures, so recursion is a natural way of making life easy. Using recursion in TSQL is like Sumo Wrestlers doing Ballet. It is possible but not pretty.

The implementation

Although the code for the JSON Parser/Shredder will run in SQL Server 2005, and even in SQL Server 2000 (with some modifications required), I couldn’t resist using a TVP (Table Valued Parameter) to pass a hierarchical table to the function, ToJSON, that produces a JSON ‘document’. Writing a SQL Server 2005 version should not be too hard.

First the function replaces all strings with tokens of the form @Stringxx, where xx is the foreign key of the table variable where the strings are held. This takes them, and their potentially difficult embedded brackets, out of the way. Names are  always strings in JSON as well as  string values.

Then, the routine iteratively finds the next structure that has no structure contained within it, (and is, by definition the leaf structure), and parses it, replacing it with an object token of the form ‘@Objectxxx‘, or ‘@arrayxxx‘, where xxx is the object id assigned to it. The values, or name/value pairs are retrieved from the string table and stored in the hierarchy table. Gradually, the JSON document is eaten until there is just a single root object left.

The JSON outputter is a great deal simpler, since one can be surer of the input, but essentially it does the reverse process, working from the root to the leaves. The only complication is working out the indent of the formatted output string.

In the implementation, you’ll see a fairly heavy use of PATINDEX. This uses a poor man’s RegEx, a starving man’s RegEx. However, it is all we have, and can be pressed into service by chopping the string it is searching (if only it had an optional third parameter like CHARINDEX that specified the index of the start position of the search!). The STUFF function is also a godsend for this sort of string-manipulation work.

So once we have a hierarchy, we can pass it to a stored procedure. As the output is an adjacency list, it should be easy to access the data. You might find it handy to create a table type if you are using SQL Server 2008. Here is what I use. (Note that if you drop a Table Valued Parameter type, you will have to drop any dependent functions or procedures first, and re-create them afterwards).

ToJSON. A function that creates JSON Documents

Firstly, we need a simple utility function:

And now, the function that takes a JSON Hierarchy table and converts it to a JSON string.

ToXML. A function that creates XML

The function that converts a hierarchy  table to XML gives us a JSON to XML converter. It is surprisingly similar to the previous function

This provides you the means of converting a JSON string into XML

This gives the result…


The so-called ‘impedence-mismatch’ between applications and databases is, I reckon, an illusion. The object-oriented nested data-structures that we receive from applications are, if the developer has understood the data correctly,  merely a perspective from a particular entity of the relationships it is involved with. Whereas it is easy to shred XML documents to get the data from it to update the database, it has been trickier with other formats such as JSON. By using techniques like this, it should be possible to liberate the application, or website, programmer from having to do the mapping from the object model to the relational, and spraying the database with ad-hoc TSQL  that uses the base tables or updateable views.  If the database can be provided with the JSON, or the Table-Valued parameter, then there is a better chance of  maintaining full transactional integrity for the more complex updates.

The database developer already has the tools to do the work with XML, but why not the simpler, and more practical JSON? I hope these two routines get you started with experimenting with this.

Interesting JSON-related articles and sites

Since writing this article, Phil has also developed a CSV parser and output and an XML parser (Producing JSON Documents from SQL Server queries via TSQL)

How can I send the “&” (ampersand) character via AJAX?


I want to send a few variables and a string with POST method from JavaScript. I get the string from the database, and then send it to a PHP page. I am using XMLHttpRequest object. The problem is that the string contains the character “&” few times, and $_POST array in PHP sees it like multiple keys. I tried replacing the “&” with “\&” with replace() function, but doesn’t seem to do anything. Can anyone help?

The javascript code and the string looks like this:

var wysiwyg = dijit.byId("wysiwyg").get("value");
var wysiwyg_clean = wysiwyg.replace('&amp;','\&amp;');

var poststr = "act=save";

String is:

<span class="style2">&amp;quot;Busola&amp;quot;</span>

You can use encodeURIComponent().

It will escape all the characters that cannot occur verbatim in URLs:

var wysiwyg_clean = encodeURIComponent(wysiwyg);

In this example, the ampersand character & will be replaced by the escape sequence %26, which is valid in URLs.