SQL Server

Create a JSON Array of Objects

See more JSON Examples

Demonstrates how to create a JSON array of objects. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    DECLARE @arr int
    EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @arr OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @obj int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @obj OUT

    -- Create a new and empty JSON object in the 1st position of the JSON array 
    -- and return the reference in the last argument.
    EXEC sp_OAMethod @arr, 'AddObjectAt2', @success OUT, 0, @obj
    EXEC sp_OAMethod @obj, 'UpdateString', @success OUT, 'Name', 'Otto'
    EXEC sp_OAMethod @obj, 'UpdateInt', @success OUT, 'Age', 29
    EXEC sp_OAMethod @obj, 'UpdateBool', @success OUT, 'Married', 0

    -- Create a new and empty JSON object in the 2nd position of the JSON array 
    -- and return the reference in the last argument.
    EXEC sp_OAMethod @arr, 'AddObjectAt2', @success OUT, 1, @obj
    EXEC sp_OAMethod @obj, 'UpdateString', @success OUT, 'Name', 'Connor'
    EXEC sp_OAMethod @obj, 'UpdateInt', @success OUT, 'Age', 43
    EXEC sp_OAMethod @obj, 'UpdateBool', @success OUT, 'Married', 1

    -- Create a new and empty JSON object in the 3rd position of the JSON array 
    -- and return the reference in the last argument.
    EXEC sp_OAMethod @arr, 'AddObjectAt2', @success OUT, 2, @obj
    EXEC sp_OAMethod @obj, 'UpdateString', @success OUT, 'Name', 'Ramona'
    EXEC sp_OAMethod @obj, 'UpdateInt', @success OUT, 'Age', 34
    EXEC sp_OAMethod @obj, 'UpdateBool', @success OUT, 'Married', 1

    -- Examine what we have:
    EXEC sp_OASetProperty @arr, 'EmitCompact', 0
    EXEC sp_OAMethod @arr, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- The output is:

    -- [
    --   {
    --     "Name": "Otto",
    --     "Age": 29,
    --     "Married": false
    --   },
    --   {
    --     "Name": "Connor",
    --     "Age": 43,
    --     "Married": true
    --   },
    --   {
    --     "Name": "Ramona",
    --     "Age": 34,
    --     "Married": true
    --   }
    -- ]

    EXEC @hr = sp_OADestroy @arr
    EXEC @hr = sp_OADestroy @obj


END
GO