SQL Server

Iterate over JSON Array containing JSON Objects

See more JSON Examples

Demonstrates how to load a JSON array and iterate over the JSON 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
    DECLARE @success int
    SELECT @success = 0

    -- Loads the following JSON array and iterates over the objects:
    -- 
    -- [
    -- {"tagId":95,"tagDescription":"hola 1","isPublic":true},
    -- {"tagId":98,"tagDescription":"hola 1","isPublic":true},
    -- {"tagId":101,"tagDescription":"hola 1","isPublic":true},
    -- {"tagId":104,"tagDescription":"hola 1","isPublic":true},
    -- {"tagId":107,"tagDescription":"hola 1","isPublic":true},
    -- {"tagId":110,"tagDescription":"hola 1","isPublic":true},
    -- {"tagId":113,"tagDescription":"hola 1","isPublic":true},
    -- {"tagId":114,"tagDescription":"hola 2","isPublic":true},
    -- {"tagId":111,"tagDescription":"hola 2","isPublic":true},
    -- {"tagId":108,"tagDescription":"hola 2","isPublic":true},
    -- {"tagId":105,"tagDescription":"hola 2","isPublic":true},
    -- {"tagId":102,"tagDescription":"hola 2","isPublic":true},
    -- {"tagId":99,"tagDescription":"hola 2","isPublic":true},
    -- {"tagId":96,"tagDescription":"hola 2","isPublic":true},
    -- {"tagId":97,"tagDescription":"hola 3","isPublic":true},
    -- {"tagId":100,"tagDescription":"hola 3","isPublic":true},
    -- {"tagId":103,"tagDescription":"hola 3","isPublic":true},
    -- {"tagId":106,"tagDescription":"hola 3","isPublic":true},
    -- {"tagId":109,"tagDescription":"hola 3","isPublic":true},
    -- {"tagId":112,"tagDescription":"hola 3","isPublic":true},
    -- {"tagId":115,"tagDescription":"hola 3","isPublic":true},
    -- {"tagId":93,"tagDescription":"new tag","isPublic":true},
    -- {"tagId":94,"tagDescription":"new tag","isPublic":true},
    -- {"tagId":89,"tagDescription":"tag 1","isPublic":true},
    -- {"tagId":90,"tagDescription":"tag 2","isPublic":true},
    -- {"tagId":91,"tagDescription":"tag private 1","isPublic":false},
    -- {"tagId":92,"tagDescription":"tag private 2","isPublic":false}
    -- ]

    -- Load a file containing the above JSON..
    DECLARE @sbJsonArray int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbJsonArray OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @sbJsonArray, 'LoadFile', @success OUT, 'qa_data/json/arraySample.json', 'utf-8'

    DECLARE @arr int
    EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @arr OUT

    EXEC sp_OAMethod @arr, 'LoadSb', @success OUT, @sbJsonArray

    DECLARE @tagId int

    DECLARE @tagDescription nvarchar(4000)

    DECLARE @isPublic int

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count int
    EXEC sp_OAGetProperty @arr, 'Size', @count OUT
    DECLARE @obj int

    WHILE @i < @count
      BEGIN
        EXEC sp_OAMethod @arr, 'ObjectAt', @obj OUT, @i
        EXEC sp_OAMethod @obj, 'IntOf', @tagId OUT, 'tagId'
        EXEC sp_OAMethod @obj, 'StringOf', @tagDescription OUT, 'tagDescription'
        EXEC sp_OAMethod @obj, 'BoolOf', @isPublic OUT, 'isPublic'


        PRINT 'tagId: ' + @tagId

        PRINT 'tagDescription: ' + @tagDescription

        PRINT 'isPublic: ' + @isPublic

        PRINT '--'

        EXEC @hr = sp_OADestroy @obj

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @sbJsonArray
    EXEC @hr = sp_OADestroy @arr


END
GO