(SQL Server) JSON Insert Empty Array or Object

Demonstrates how to use the UpdateNewArray and UpdateNewObject methods to insert an empty array or object.

Note: The UpdateNewArray an UpdateNewObject methods were introduced in Chilkat v9.5.0.75.

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

    DECLARE @success int

    -- The following code builds the following JSON, which contains both an empty array and empty object:

    -- 	{
    -- 	  "abc": {
    -- 	    "xyz": [
    -- 	      {
    -- 	        "Name": "myName",
    -- 	        "Description": "description",
    -- 	        "ScheduleDefinition": "schedule definition",
    -- 	        "ExceptionScheduleDefinition": "",
    -- 	        "Attribute": [
    -- 	        ],
    -- 	        "SomeEmptyObject": {},
    -- 	        "token": "token"
    -- 	      }
    -- 	    ]
    -- 	  }
    -- 	}

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'abc.xyz[0].Name', 'myName'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'abc.xyz[0].Description', 'description'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'abc.xyz[0].ScheduleDefinition', 'schedule definition'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'abc.xyz[0].ExceptionScheduleDefinition', ''
    EXEC sp_OAMethod @json, 'UpdateNewArray', @success OUT, 'abc.xyz[0].Attribute'
    EXEC sp_OAMethod @json, 'UpdateNewObject', @success OUT, 'abc.xyz[0].SomeEmptyObject'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'abc.xyz[0].token', 'token'

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @json


END
GO