(SQL Server) Create a JSON Array of Objects

Demonstrates how to create a JSON array of objects.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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 @arr int
    -- Use "Chilkat_9_5_0.JsonArray" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @arr OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Add an empty object at the 1st JSON array position.
    EXEC sp_OAMethod @arr, 'AddObjectAt', @success OUT, 0
    -- Get the object we just created.
    DECLARE @obj int
    EXEC sp_OAMethod @arr, 'ObjectAt', @obj OUT, 0
    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
    EXEC @hr = sp_OADestroy @obj

    -- Add an empty object at the 2nd JSON array position.
    EXEC sp_OAMethod @arr, 'AddObjectAt', @success OUT, 1
    EXEC sp_OAMethod @arr, 'ObjectAt', @obj OUT, 1
    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
    EXEC @hr = sp_OADestroy @obj

    -- Add an empty object at the 3rd JSON array position.
    EXEC sp_OAMethod @arr, 'AddObjectAt', @success OUT, 2
    EXEC sp_OAMethod @arr, 'ObjectAt', @obj OUT, 2
    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
    EXEC @hr = sp_OADestroy @obj

    -- 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


END
GO