Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 91 of 212

79 C H A P T E R 5 | Broader data access geography Other uniqueidentifier money string In addition, the database engine escapes special characters as shown in the following table: Special character Escape sequence Double quotation marks (") \" Backslash (\) \\ Forward slash (/) \/ Backspace \b Form feed \f New line \n Carriage return \r Horizontal tab \t When you use the AUTO argument with the FOR JSON clause, as shown in Example 5-12, the database engine automatically converts data types and formats and escapes the results of a SELECT statement according to JSON syntax as an array of objects. Each row in the result set is an object in the array, and each row contains name/value pairs consisting of the column name and the cell value for that row. Example 5-12: Creating JSON output from a single table with FOR JSON AUTO SELECT TOP 3 p.ProductID, p.Name FROM Production.Product p FOR JSON AUTO; /* Result [{"ProductID":1,"Name":"Adjustable Race"}, {"ProductID":879,"Name":"All-Purpose Bike Stand"}, {"ProductID":712,"Name":"AWC Logo Cap"}] */ When you combine two tables in the SELECT statement and include the FOR JSON AUTO clause, the structure of the JSON output changes, as shown in Example 5-13. With FOR JSON AUTO, you control the formatting by controlling the order of the columns. The columns from the table listed first in the column list are a first-level object in the JSON output array. Columns for the second table are grouped together as a second-level object nested inside the first-level object. This second-level object's name is the table alias, and its value is an array of the name/column pairs for the second table's columns. Example 5-13: Creating JSON output from two tables with FOR JSON AUTO SELECT TOP 2 p.ProductID, p.Name, pr.ProductReviewID, pr.ReviewerName, pr.EmailAddress, pr.Rating, pr.ReviewDate FROM Product.ProductReview pr INNER JOIN Production.Product p ON pr.ProductID = p.ProductID FOR JSON AUTO; /* Result [{"ProductID":709,"Name":"Mountain Bike Socks, M", "pr":[

Articles in this issue

Archives of this issue

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