(SQL Server) Create a JSON Array Containing an Object

Creates a top-level JSON array containing an object.

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

    DECLARE @success int
    EXEC sp_OAMethod @jArray, 'AddObjectAt', @success OUT, 0
    DECLARE @json int
    EXEC sp_OAMethod @jArray, 'ObjectAt', @json OUT, 0

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'groupId', ''
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'sku', ''
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'title', ''
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'barcode', ''
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'category', ''
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'description', ''
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'images[0]', 'url1'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'images[1]', 'url...'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'isbn', ''
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'link', ''
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'linkLomadee', ''
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'prices[0].type', ''
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'prices[0].price', '0'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'prices[0].priceLomadee', '0'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'prices[0].priceCpa', '0'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'prices[0].installment', '0'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'prices[0].installmentValue', '0'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'productAttributes."Atributo 1"', 'Valor 1'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'productAttributes."Atributo ..."', 'Valor ...'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'technicalSpecification."Especificação 1"', 'Valor'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'technicalSpecification."Especificação ..."', 'Valor ...'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'quantity', '0'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'sizeHeight', '0'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'sizeLength', '0'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'sizeWidth', '0'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'weightValue', '0'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'declaredPrice', '0'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'handlingTimeDays', '0'
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'marketplace', 0
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'marketplaceName', ''

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

    -- The output of this program is:

    -- [
    --   {
    --     "groupId": "",
    --     "sku": "",
    --     "title": "",
    --     "barcode": "",
    --     "category": "",
    --     "description": "",
    --     "images": [
    --       "url1",
    --       "url..."
    --     ],
    --     "isbn": "",
    --     "link": "",
    --     "linkLomadee": "",
    --     "prices": [
    --       {
    --         "type": "",
    --         "price": 0,
    --         "priceLomadee": 0,
    --         "priceCpa": 0,
    --         "installment": 0,
    --         "installmentValue": 0
    --       }
    --     ],
    --     "productAttributes": {
    --       "Atributo 1": "Valor 1",
    --       "Atributo ...": "Valor ..."
    --     },
    --     "technicalSpecification": {
    --       "Especificação 1": "Valor",
    --       "Especificação ...": "Valor ..."
    --     },
    --     "quantity": 0,
    --     "sizeHeight": 0,
    --     "sizeLength": 0,
    --     "sizeWidth": 0,
    --     "weightValue": 0,
    --     "declaredPrice": 0,
    --     "handlingTimeDays": 0,
    --     "marketplace": false,
    --     "marketplaceName": ""
    --   }
    -- ]

    EXEC @hr = sp_OADestroy @jArray


END
GO