XML Data to JSON

Introduction

This article describes how to transfer XML data from SQL Server to a JSON object in a web browser, using the powerful Newtonsoft JSON converter. This article assumes you are familiar with basic concepts in SQL Server, VB.NET, HTML and JavaScript coding.

You will need the Json.Net DLL from James Newton-King. http://nuget.org/packages/newtonsoft.json/, which you can install directly in Visual Studio from the "Project/Manage NuGet Packages..." menu. Just search for "json.net".

Background

Ajax is a great tool for retrieving data from a web server for processing in a client web browser. Ajax enables you to manipulate DOM elements in Javascript, and is very easy to use with the JQuery library. The big question is what type of data you will transfer. You have plentiful choices. Some of them are:

• Generate HTML on the server, and insert it directly in the browser
• Use JSONP to retrieve JavaScripts directly from the server
• Generate datasets which you parse on the client
• Generate XML and parse it with JQuery
• Generate JSON and parse it with JQuery

The latter method gives you great flexibility, both on the server and on the client. It is fast, and you get away with minimal coding. JSON is human readable text, and is smaller and faster to process than XML. One problem is that SQL Server does not currently support JSON output. So you will either have to get XML data from the database, or build datasets in a .NET method. Building datasets in a method can be cumbersome, and requires additional processing. Since SQL Server has native support for XML, we choose this path in this article.

All code in this article is compatible with SQL Azure.

Using the Code

To get started, let's create some data in the database. We will output this data as XML from SQL Server, convert it to JSON in a web service, and parse it in JavaScript using JQuery Ajax. Let's say we have a table of users in the database:

create table Users (UserID int identity(1,1) not null primary key, Username nvarchar(50) not null)
insert into Users (Username) values ('Karen')
insert into Users (Username) values ('John')
insert into Users (Username) values ('Elise')  

This is a very simple data table, but enough for our intentions in this article.

SQL Server has great support for XML output. I recommend using FOR XML PATH to format the XML, as it gives you great flexibility with parent/child relationships.

Using stored procedures to return data to the web server makes your code safe, fast, and flexible. If you use stored procedures only, you can GRANT EXECUTE to the SQL Server user, and no one will be able to see data that does not come from a stored procedure. Stored procedures are faster than views or SELECT statements, as SQL Server caches statistics on the stored procedure. In addition, you can change underlying views and tables without affecting the output from the database.

A great performance tip for SQL Server is to always create views on tables, and then selecting from those views in your stored procedure. This is the optimal way for SQL Server to create its execution plan. You could also consider using SQL Server functions to return XML data. This will increase your flexibility, and reduce the amount of code.

To return an XML with all the users in the database, create a stored procedure:

create proc GetAllUsers() as
begin
  set nocount on;
  declare @xml XML
  SET @xml = (
       SELECT [UserID] "User/@UserID", [Username] "User/@Username"
       FROM [Users]
       FOR XML PATH(''), TYPE, ROOT('Users')
       )
   SELECT @xml
end

In this procedure, we have declared the variable @xml of type XML, which is a native datatype in SQL Server. You can manipulate this datatype in SQL Server by using the OPENXML command, but this is out of the scope of this article. The procedure above will return the XML:

<Users>
  <User UserID="1" Username="Karen"/>
  <User UserID="2" Username="John"/>
  <User UserID="3" Username="Elise"/>
</Users>

As you might notice, all XML nodes above are self-closing. You might prefer to put text like Usename in the node's innerText instead, which you are free to do. This will have a small implication on how you parse the data in JavaScript.

You can test the above procedure by typing "EXECUTE GetAllUsers" in a SQL Server query window.

Now that we have some data, we want our webserver to deliver it to requesting clients. You have some options here. The easy way is to create a web form, and return the data as a normal HTTP response with response.Write(). We will use web services, as it provides more flexibility and better security. Notice that if you use .NET sessions, the function returning your data needs the EnableSession attribute:

<WebMethod(EnableSession:=True)>

This will enable you to get the current .NET session, which you can use to identify the user.

We will now create a webservice called MyWebService, which will get XML from the database and return JSON to requesting clients. There are two strategies to select from here. You can either return a .NET object serialized as a JSON object, or you can return the JSON as text to the JavaScript client and parse it there. This is a matter of preference. Personally, I try to avoid strongly typed data between servers and clients, especially in a HTTP environment, and return text to the client. This might be a pitasecond slower than strongly typed data, but is way more flexible and maintainable, both for the client and server developer. In this article, we choose to return text to the client, and parse it with the built-in JSON parser that comes with any modern browser.

If you need to support really old browsers, use a third party JSON parser like json3 for JavaScript. In any case, you can use JQuery's $.parseJSON().

Now let's create our web service. To do this, add a web service "MyWebService.asmx" to a directory "/webservices" in your project. Replace all the example code in the web service with:

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Web.Script.Services
Imports System.ComponentModel
Imports System.Xml
Imports Newtonsoft.Json

<ScriptService()> _
<WebService(Namespace:="http://www.example.com")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class MyWebService
  Inherits WebService

  <WebMethod()> _
  Public Function GetAllUsers() as String
    Dim dc As New SqlClient.SqlCommand
    Dim con as New SqlClient.SqlConnection
    Dim mydoc as New System.Xml.XmlDocument
 
    con.ConnectionString = "<your connection string>"   
    con.Open()
    dc.Connection = con
    dc.CommandType = CommandType.StoredProcedure 
    dc.CommandText = "GetAllUsers"
    mydoc.LoadXML(dc.ExecuteScalar())
    con.Close()

    Return JsonConvert.SerializeXmlNode(mydoc, Newtonsoft.Json.Formatting.Indented)
 End Function 

End Class   

Notice the ScriptService() attribute on the class. This will enable IIS to respond to scripting requests like Ajax. You can replace the Namespace with anything you want, like the name of your website. Make sure you enter the correct connection string to your database. The web service will return a JSON object with the XML data in a string:

{d: "{
 "Users": {
 "User": [
 {
 "@UserID": "1",
 "@Username": "Karen"
 },
 {
 "@UserID": "2",
 "@Username": "John"
 },
 {
 "@UserID": "3",
 "@Username": "Elise"
 }
 ]} 
 }"
} 

This is a JSON object with only one entry: d! All the nice XML you got from the database is wrapped as text in the contents of the d object. As you can see, this text is as readable as XML. A nice, but unintended feature, is that the XML attributes have the same name in JSON as in your stored procedure.

Before you continue, your web page must reference the JQuery library. You can either refer it directly by CDN, or download it from http://www.jquery.com. After you have included JQuery, paste the following code anywhere in your web page:

<script type="text/javascript">
$(function () {  // Code in this function will run when web page is loaded in the browser.
   DisplayUsers();
});

var DisplayUsers = function() {
    var Username;

    $.ajax({
       url: "/webservices/MyWebService.asmx/GetAllUsers"
       data: "",                                             // Parameters to the web service.
       contentType: "application/json; charset=utf-8",       // Tells the web server how to respond.
       dataType: "json",                                     // Datatype JSON.
       type: "POST",                                         // HTTP method.
       async: true                                           // Asynchronous processing.
    }).done(function (rtn) { 
       var mydata = JSON.parse(rtn.d);                       // Parse the JSON text.
       if (mydata.Users) {                                   // In case XML is empty.
          $(mydata.Users.User).each(function() {             // Loop all users.
             Username = $(this).prop("@Username");
             $(document.body).append(
                $("<div>").text(Username)   // Append a DIV to the document body.
             );
          });
       }
    });
</script>  

As mentioned above, you can replace JSON.parse() with $.parseJSON(). Notice that we use JQuery's prop() method to get XML attributes. If you have data inside an XML node, you can reference it directly. Say you have:

<Users>
  <User UserID="1"><Username>Karen</Username></User>
  <User UserID="2"><Username>John</Username></User>
  <User UserID="3"><Username>Elise</Username></User>
</Users>       

Then you would get Elise's name with:

this.Username;   // Instead of $(this).prop("@Username");

In case you have large XML documents, you might need to set maxJsonLength="[numberOfBytes]" in your web.config file. In the <configuration> section, add:

<system.web.extensions>
  <scripting>
    <webServices>
      <jsonSerialization maxJsonLength="500000"></jsonSerialization>
    </webServices>
  </scripting>
</system.web.extensions>

I hope you enjoyed this article, and that your skills as a .NET Ajax developer have improved. Feel free to post comments or contact me directly. Thank you!

History

• First submission