Json_To_GlobalTable
Transforms a JSON string into a global temporary table
- Free Stored Procedure
- SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017
EXEC SQLHTTP.net.Json_To_GlobalTable @Json, @GlobalTableName, [@Style], [@SuppressMessage]
| Name | Type | Description |
|---|---|---|
| @Json | nvarchar(MAX) | Required. JSON string |
| @GlobalTableName | varchar(100) | Required. Name of the global temporary table to be created and populated using this stored procedure |
| @Style | tinyint | Optional. Currently two styles are available for slightly different representation of certain column names. Default: 0 |
| @SuppressMessage | bit | Optional. Boolean indicator what displays or suppresses an output message with alternative SQL Code that does not utilize global temporary tables. |
Use this stored procedure sparingly when using Json_To_Xml and then utilizing XQueryHelper is not a good option.
This stored procedure will display a message containing alternative SQL code that DOES NOT utilize temporary global tables. However, since the query relies on ordinal positions of columns, any slight change in the JSON data structure may affect the aliasing of column names. See below.
|
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 |
DECLARE @Json nvarchar(MAX) SET @Json = '{ "firstName": "John", "lastName": "Smith", "age": 25, "address": { "streetAddress": "21 2nd Street", "city": "New York" }, "phoneNumbers": [ { "type": "home", "number": "212 555-1234" }, { "type": "office", "number": "646 555-4567" }, { "type": "mobile", "number": "123 456-7890" } ] }' EXEC SQLHTTP.net.Json_To_GlobalTable @Json, 'TempGlobalTable' SELECT * FROM ##TempGlobalTable |
|
1 2 3 4 5 6 7 8 9 10 |
RowID ParentRowID NodePath address_city address_streetAddress age firstName lastName phoneNumbers_number phoneNumbers_type ------- ------------- ---------------------------------- -------------- ----------------------- ---- ---------- ---------- --------------------- ----------------- 1 0 NoName NULL NULL 25 John Smith NULL NULL 2 1 NoName/address{1} New York 21 2nd Street 25 John Smith NULL NULL 8 1 NoName/phoneNumbers{1} NULL NULL 25 John Smith NULL NULL 9 8 NoName/phoneNumbers{1}/NoName{1} NULL NULL 25 John Smith 212 555-1234 home 12 8 NoName/phoneNumbers{1}/NoName{2} NULL NULL 25 John Smith 646 555-4567 office 15 8 NoName/phoneNumbers{1}/NoName{3} NULL NULL 25 John Smith 123 456-7890 mobile |
In addition to the above output, the following will be displayed in SSMS Messages tab when @SuppressMessage = 1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
*************************************************************************************************************************** * An alternative query without the use of a temporary GLOBAL table is shown below. However, since the query below relies * * on ordinal positions of columns, any slight change in the JSON data structure may affect the aliasing of column names. * * To suppress this message, include @SuppressMessage = 1 parameter to your call to SQLHTTP.net.Json_To_GlobalTable * *************************************************************************************************************************** SELECT RowID, ParentRowID, NodePath, Col4 AS [address_city], Col5 AS [address_streetAddress], Col6 AS [age], Col7 AS [firstName], Col8 AS [lastName], Col9 AS [phoneNumbers_number], Col10 AS [phoneNumbers_type] FROM SQLHTTP.net.Json_To_OrdinalTable(@Json) |