Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Putting JSON on an equal footing to XML

Putting JSON on an equal footing to XML

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51759 Visits: 40308

I know I'll probably be a voice of 1 but I have a serious hatred for most of markup languages. HTML is a fun little thing kind of like "Reveal codes" was fun in Word Perfect and it makes for some simple formatting. XML seems to be used primarily for data transfer and I hate it for multiple reasons. Even XML's so called "attributes" easily more than double or triple the size of the file (I've seen files with ratios in the hundreds). For the sake of argument, I'll just say that it doubles the size of most tabular data. That doubles the size for storage on disk, usually more than doubles the size in memory because of the cruddy "edge" tables that get built behind the scenes to parse XML, doubles the transmission time because there are at least double the number of bytes just from the tags never mind always being character based instead of 1, 2, or 4 byte integer based for most numbers, doubles the size of the cooling systems I need because everything has to work at least twice as hard to parse the data, etc, etc, etc. Heh... and who in the world actually thinks it's a good thing to depend on nested non-unique tags to build a hierarchy with? And then people clog up my databases with this crap because the "need to use the XML all at once" from the front-end. Yeah... the real reason they need it that way is because a lot of them don't know how to parse it and normalize it before dropping it into a database. Don't even get me started on EDI! :-P

Phil wrote an article a while back where he also (IMHO) appeared to be a bit disgusted with the byte-bloat caused by markup languages for the transmission of data. He suggested that someone come up with a better way and I was going to jump at the chance except for one thing... one of the best ways to do all that was invented scores of years ago. Lookup ASCII characters 28 through 31 and see just how simple and fast data transmission used to be. If we reverted to such a thing for the transmission of "unformatted" (i.e. not for display purposes) with a little common sense of transmitting byte values for numbers instead of characters, most of us would never have a "performance issue" with our networks, storage systems, etc. Instead of having bottlenecks, our data would be like a BB shot from a rail-cannon through the Holland tunnel instead of like trying to drive a pig truck through a Coke bottle.

We just don't need the overhead of languages like XML to transmit even some of the most complicated data. I won't get into what a travesty I think it is to transmit tagged hierarchical data to an RDBMS even wheen it seems like a good idea. And, no, we don't need formatting tags (even when CSS is available) on every bloody element of data even for the front end. Assign a "default" and only include formatting for exceptions. I know that's the way that a lot of good people try to do it but, apparently, none of the people sending me XML data are "good" people. :-D

And before you folks blocking the back door of this club go postal on me about human readability, isn't THAT what the presentation layer is for??? Whistling


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 2551
Actually, while XML in readable form is pretty bloated due to it being so verbose (hard to argue with you there). For storage however it can be very compact when done right. This is due to its high compressability, and I do not mean tools like zip and rar, but more specialised compressors. Where a normal compressor needs to build up a model of the data, one with xml/schema knowledge gets a model handed to him, improving the compression.

As for its use in databases as a type, here we don't use the xml type at all and strore it all as text instead. The intersting or frequently accessed bits we work with are extracted by the application and stored in separate fields next to the plain text xml. Storing XML along with the attributes we use does have an advantage to us in three ways:

1. We (and the customer) can quickly see what the source was of the data we work with and check this out when an error is suspected.
2. We can extend the working set of attributes and populate the new ones by extracting them from the xml (with a sql statement if need be).
3. We have the context of the data we use and thus can do some analysis on historical data. Good to have as an option for marketing purposes.

JSON would I think not be suited for any of these points.

As for readability, i love to drag an xml fragment to my browser (IE) and see a nicely readable source, which makes it easy to spot problems, be it programming errors on our side or simply wrong data delivery.

And I agree quite a bit with you Jeff, on the sorry state of current markup languages, especially HTML. In many ways, it feels like old basic with line numbers and no labels or functions all over again. The source of many modern interactive pages is often uncomprehensible by anyone that did not work on the page just a minute ago. That is why developers (me too) are bending themselfs in all kinds of application modeling excersises as we need to get this "mess" under control.
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4259 Visits: 72513
I'm dragging myself into figuring out Json, because of course we need to store it... and query it.

What I don't get is why to me it looks so much like XML, even the types of queries I've seen tossed my way as possibilities for how the devs would want to query seem to be Xpath type queries.

Why hasn't anybody written something to not just convert JSON to XML (for storage into SQL, which I've found a lot of), but to translate JSON queries to XML path style queries?

I figure it would have to be CLR, but when it's that or running Mongo side by side (and having to maintain data in both the SQL side and the Mongo side) it seems that it should exist.

--Mark Tassin
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5
I have developed a JSON type for SQL Server available for purchase at It is a port of the binary JSON type just released for PostgreSQL.

It also offers:

* Table value functions for returning elements of JSON objects / columns as a result set
* A fluent API for creating, modifying, and accessing object properties
* JSONPath expressions to set, remove, and get data within JSON objects (similar to XPath expressions for XML)
* Aggregations for converting result sets into objects or arrays
* Binary storage for fast data access without reparsing


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum