In SQL Server, we treat XML with a reverence that is disproportionate to its importance. This hit me whilst reading up on XML. I’ve come a bit late to Michael Coles’ wonderful ‘Pro SQL Server 2008 XML’. It is the first book on the subject of using XML in SQL Server that I’ve enjoyed, and that is because Michael is keen to put XML firmly in the context of the needs of the database developer. He has all the right instincts. However, to see the extent to which XML has insinuated itself into SQL Server so eloquently laid out only makes me even more baffled as to why the same hasn’t been done for JSON, or even HTML; both of which are dominant, human-readable, platform independent, open data-exchange formats that can be used wherever applications need to exchange or store structured information as text, and which may need to be shredded by SQL Server.

For all its obvious faults, JSON has become more dominant as a data-transfer markup. Data formatted according to the JSON standard is lightweight and can be parsed by JavaScript implementations easily.  It is used most obviously in AJAX, but also in the OData or  GData protocols  for reading, writing, and modifying information on the web. Databases use it:   CouchDB and MongoDB has a RESTful JSON API to allow javascript applications to access data easily. It forms the basis of  Google Freebase’s Metaweb Query Language. Microsoft use it within the DACPAC (Data-tier Application Component Packages). Adobe Air applications usually make use of JSON for all serialisation.

The SQL Server team have indeed gone big on XML, as Michael Cole’s book reveals. The industry has gone big in another direction: JSON.  So why not JSON? When designing database-application interfaces,  the question often comes up as to whether all data exchange can be done with JSON now that the HTML5 /Javascript paradigm has become so common. We can use the XML extensions to SQL Server to talk XML with applications, and that is great but it is becoming more common to be asked for JSON.  There is a rather poor JSON module in .NET, but nothing in SQL Server, and I’ve heard of nothing in the planning stage. In the house of SQL Server, XML is the plump laddie with his legs under the table, whereas poor JSON is scraping up gleanings in a stable.