Json_To_OrdinalTable

Returns tabular data representation of a JSON string

  • Free Function
  • SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017

Syntax:

SELECT *
FROM SQLHTTP.net.Json_To_OrdinalTable( @Json )

Arguments:

Name Type Description
@Json nvarchar(MAX) JSON string to be shredded into a table

Table Returned:

Column name Data type Description
RowID int Auto-generated unique ID
ParentRowID int Parent RowID, if any.
NodePath nvarchar(MAX) Path to current position in the Json string or the XML Value. Note that this path is similar to XPath but sqaure brackets were replaced with curly brackets in order to simplify using this value in a WHERE clause
Col4 nvarchar(MAX) JSON data of a String, Number or Boolean
Col5 nvarchar(MAX) JSON data of a String, Number or Boolean
.
.
.
Col1024 nvarchar(MAX) JSON data of a String, Number or Boolean

Remarks:

Use of this function is often preceded by executing the Json_To_GlobalTable stored procedure which will also generate a SQL query for using this function with the columns properly aliased. This function is intended for users who wish to avoid using XPath syntax (by first converting a Json string to XML using the Json_To_XML function and then utilizing the XQueryHelper stored procedure).

Sample Usage:

See Also: