The first trick is to represent it as a Adjacency list hierarchy in a table. We then pass it to a separate function, ToJSON, to render it as JSON. Markup languages such as JSON and XML all represent object data as hierarchies. What we’ve done here is to convert the result into an intermediary SQL Table which is defined by a hierarchy type. We then just create the SQL we want, get it to create XML and the rest is simple.īack to AdventureWorks 2008, and here is an example of its use. All we have to do is to add a new Table-Valued function that produces a hierarchy from XML. There is already a function that produces a JSON document from such a table. For handling the type of hierarchical information that is transferred via JSON, CSV or XML, I’ve published a variety of stored procedures and functions that all use a rather crude hierarchy table that is sufficient for the purpose. If you’re not too concerned with performance, you can experiment with some functions I’ve published over the years for dealing with data documents. If you have a generic way to convert from any XML document, whether derived from SQL or not, to JSON, then it becomes even more useful! The advantage of using XML is that you can make use of the versatility of the WITH XML PATH syntax, or the XPath SQL Server extensions, to specify the hierarchy. When I say ‘simple’ I mean simpler than any other alternative. Then you parse the XML, which has obligingly converted all the values into string form, gives you the names, their position in the hierarchy and even the DataType. The simplest strategy to provide a general way to output JSON is to convert your SQL result to XML by using the FOR XML syntax of the SELECT statement. In this article, I’ll be introducing a few ideas about providing a generic way to produce JSON and other types of data documents, from SQL. Until SQL Server provides ‘native’ JSON support it will be rather slow, but not difficult. However, what if the developers needed to have large variety of results, maybe even from SQL created by the application rather than the database? That’s cool, though there will be a performance hit to using a generic solution that will produce JSON from any SQL. You would normally craft the SQL to do this ‘by hand’ for the specific job, using one of a variety of techniques. If you need to provide JSON-based results from the database, you are faced with a problem, because SQL Server doesn’t, at the time of writing this, have native JSON-integration (see the article ‘ Consuming hierarchical JSON documents in SQL Server using OpenJSON‘ in the list above for a SQL Server 2016 solution). Importing JSON data from Web Services and Applications into SQL Server(October 2017).Consuming hierarchical JSON documents in SQL Server using OpenJSON (Sept 2017).Producing JSON Documents From SQL Server Queries via TSQL (May 2014).SQL Server JSON to Table and Table to JSON (March 2013).Consuming JSON Strings in SQL Server (Nov 2012).Producing JSON Documents from SQL Server queries via TSQL - Simple Talk Skip to contentĪrticles by Phil Factor about JSON and SQL Server:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |