Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 96 of 212

84 C H A P T E R 5 | Broader data access 20. The result of this operation is a table with the JSON data transformed into rows and columns, as shown in Figure 5-8. Example 5-20: Using the OPENJSON function with the CROSS APPLY operator SELECT ProductID, Name, ReviewID, ReviewerName, ReviewerEmail, Rating, ReviewDate FROM Production.Product CROSS APPLY OPENJSON(Reviews) WITH ( ReviewID int '$.ReviewID', ReviewerName varchar(100) '$.Reviewer.Name', ReviewerEmail varchar(100) '$.Reviewer.Email', Rating int '$.Rating', ReviewDate datetime '$.ReviewDate' ) WHERE Reviews IS NOT NULL Figure 5-8: Rows returned by the CROSS APPLY operator and OPENJSON function. Working with a list of comma-separated values in T-SQL Applications commonly generate lists of comma-separated values in the user interface, which you later need to separate by using any of a variety of techniques, such as a comma-splitting function. As long as database compatibility is set correctly, a simple approach to working with a list of comma-separated values is to use the list as an argument in the OPENJSON function. To avoid producing an error, be sure to enclose the list in brackets. Because the list is not executed by the OPENJSON function, there is no risk of SQL injection. Furthermore, the function understands the difference between commas used to separate items in the list and commas that are included as part of a string value. Note You can find information about OPENJSON syntax at "OPENJSON (Transact-SQL),"at, and more examples of working with OPENJSON in "Use OPENJSON with the Default Schema (SQL Server),"at For information about importing entire JSON files into SQL Server, see "Importing JSON files in SQL Server" at server-using-openrowset-bulk.aspx. Using other built-in JSON functions SQL Server 2016 has additional built-in support for JSON with the following functions, which you can use in the SELECT, WHERE, or GROUP BY clause or in a constraint definition: ISJSON Returns 1 if the string argument supplied is a valid JSON structure, returns 0 if it is not, and returns null if the string argument is null. This function is demonstrated in Example 5-21.

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016