SQL Server Requires Chilkat v11.1.0+

Create a JSON Array of Objects

See more JSON Examples

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

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    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