I just did the top twenty records just so we could see them in the article, but this should be good for 20,000 in a batch.īut this is not the only way of doing it. So here we go with an example,Īlthough there is no ANSI SQL way of representing results from SQL Expressions as JSON, SELECT statements usually produce results that are represented by ‘flattened’ or ‘raw’ JSON lists, like this
The limitations are that you lose any attributes, and you have to use the simple default ‘root’ and ‘row’ structure of the XML, but none of that will matter for the common jobs since you don’t need attributes, or nesting, and if you do simple queries with FOR XML PATH, ROOT you don’t get ’em. If you just want to churn out JSON, we’ll also show you a ‘quick’ version that will dish out 20,000 rows of ‘flattened’ JSON in around ten to fifteen seconds. Firstly, this is fine for the small stuff, but this requires a lot of string manipulation, and SQL isn’t designed for doing that. As in much of life, there is some detail to attend to, which we’ll go into later. You just give the function consisting of any working SQL Query, adding the FOR XML PATH, ROOT to turn it into XML and you’ll get JSON, reflecting the structures you specify in the FOR XML query.
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). Producing JSON Documents from SQL Server queries via TSQL - Simple Talk (2022)
0 kommentar(er)
