Parse JSON in TSQL
I seem to have a huge masochistic streak in that I've written a JSON parser. It converts a JSON document into a SQL Adjacency list table, which is easy to use to update your data tables. Actually, I've done worse, in that I've done code to do the reverse process, which is to go from a hierarchy table to a JSON string
The article and code is here: Consuming Json strings in SQL server.
Select * from parseJSON('{ "Person": { "firstName": "John", "lastName": "Smith", "age": 25, "Address": { "streetAddress":"21 2nd Street", "city":"New York", "state":"NY", "postalCode":"10021" }, "PhoneNumbers": { "home":"212 555-1234", "fax":"646 555-4567" } }}')To get:
Update: As of SQL Server 2016 parsing JSON in TSQL is now possible.
Natively, there is no support. You'll have to use CLR. It is as simple as that, unless you have a huge masochistic streak and want to write a JSON parser in SQL
Normally, folk ask for JSON output from the DB and there are examples on the internet. But into a DB?
Finally SQL Server 2016 will add Native JSON support!!
Ref:
Additional capabilities in SQL Server 2016 include:
- Additional security enhancements for Row-level Security and Dynamic Data Masking to round out our security investments with Always
Encrypted.- Improvements to AlwaysOn for more robust availability and disaster recovery with multiple synchronous replicas and secondary load
balancing.- Native JSON support to offer better performance and support for your many types of your data.
- SQL Server Enterprise Information Management (EIM) tools and Analysis Services get an upgrade in performance, usability and scalability.
- Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Azure
and place your SQL Server AlwaysOn secondaries in Azure.
Features blog post: http://blogs.msdn.com/b/jocapc/archive/2015/05/16/json-support-in-sql-server-2016.aspx