NodeTable_To_Json
Constructs a JSON string from tabular data
- Free Function
- SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017
SELECT SQLHTTP.net.NodeTable_To_Json()
This procedure requires the SQLHTTP.net.NodeTable to be populated which is normally populated via the Json_To_NodeTable stored procedure.
nvarchar(MAX)
As shown in the example below, a JSON Object is “An object is an unordered set of name/value pairs”
This example demonstartes utizling the NodeTable for an Json update where “John” is replaced with “Jonathan”:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
BEGIN TRAN DECLARE @Json nvarchar(MAX) SET @Json = '{ "firstName": "John", "lastName": "Smith", "isAlive": true, "age": 25, "address": { "streetAddress": "21 2nd Street", "city": "New York", "state": "NY", "postalCode": "10021-3100" }, "phoneNumbers": [ { "type": "home", "number": "212 555-1234" }, { "type": "office", "number": "646 555-4567" }, { "type": "mobile", "number": "123 456-7890" } ], "children": [], "spouse": null }' DELETE SQLHTTP.net.NodeTable INSERT INTO SQLHTTP.net.NodeTable SELECT * FROM SQLHTTP.net.Json_To_NodeTable(@Json) UPDATE SQLHTTP.net.NodeTable SET [Value] = 'Jonathan' WHERE NodePath = 'NoName/firstName' SELECT SQLHTTP.net.FormatJson(SQLHTTP.net.NodeTable_To_Json()) DELETE SQLHTTP.net.NodeTable COMMIT TRAN |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
---------------------------------------------------- { "address": { "city": "New York", "postalCode": "10021-3100", "state": "NY", "streetAddress": "21 2nd Street" }, "age": 25, "children": [ ], "firstName": "Jonathan", "isAlive": true, "lastName": "Smith", "phoneNumbers": [ { "number": "212 555-1234", "type": "home" }, { "number": "646 555-4567", "type": "office" }, { "number": "123 456-7890", "type": "mobile" } ], "spouse": null } |