(SQL Server) Loading and Parsing a Complex JSON Array

This example loads a JSON array containing more complex data. It shows how to parse (access) various values contained within the JSON.

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)
    -- This is the JSON we'll be loading:

    -- 	[
    -- 	  {
    -- 	    "telefones": [
    -- 	      {
    -- 	        "numero": "19995555555",
    -- 	        "tipo": "T",
    -- 	        "id": 2541437
    -- 	      } 
    -- 	    ],
    -- 	    "cnpj": "11395551000164",
    -- 	    "rua": "R XAVIER AUGUSTO ROGGE, 22",
    -- 	    "complemento": "",
    -- 	    "contatos": [
    -- 	    ],
    -- 	    "tipo": "J",
    -- 	    "razao_social": "SOUP BRASIL LTDA - ME",
    -- 	    "nome_fantasia": "SOUP BRASIL",
    -- 	    "bairro": "ABC DOS COLIBRIS",
    -- 	    "cidade": "TEST",
    -- 	    "inscricao_estadual": "222.102.222.116",
    -- 	    "observacao": "",
    -- 	    "id": 2209595,
    -- 	    "ultima_alteracao": "2016-12-26 16:22:34",
    -- 	    "cep": "13555000",
    -- 	    "suframa": "",
    -- 	    "estado": "SP",
    -- 	    "emails": [
    -- 	      {
    -- 	        "email": "somebody@terra.com.br",
    -- 	        "tipo": "T",
    -- 	        "id": 1065557
    -- 	      } 
    -- 	    ],
    -- 	    "excluido": false
    -- 	  },
    -- 	  {
    -- 	    "telefones": [
    -- 	    ],
    -- 	    "cnpj": "12496555500180",
    -- 	    "rua": "AV ROLF WIEST, 100",
    -- 	    "complemento": "ANDAR 7 SALA 612 A 620",
    -- 	    "contatos": [
    -- 	    ],
    -- 	    "tipo": "J",
    -- 	    "razao_social": "SIMPLE SOFTWARE LTDA",
    -- 	    "nome_fantasia": "",
    -- 	    "bairro": "DOM ZETIRO",
    -- 	    "cidade": "APARTVILLE",
    -- 	    "inscricao_estadual": "",
    -- 	    "observacao": "",
    -- 	    "id": 2255594,
    -- 	    "ultima_alteracao": "2016-12-26 16:28:31",
    -- 	    "cep": "89255505",
    -- 	    "suframa": "",
    -- 	    "estado": "SC",
    -- 	    "emails": [
    -- 	    ],
    -- 	    "excluido": false
    -- 	  },
    -- 	  {
    -- 	    "telefones": [
    -- 	      {
    -- 	        "numero": "1938655556",
    -- 	        "tipo": "T",
    -- 	        "id": 2555438
    -- 	      } 
    -- 	    ],
    -- 	    "cnpj": "00003555500153",
    -- 	    "rua": "AV ABCDEF PINTO CATAO, 18",
    -- 	    "complemento": "",
    -- 	    "contatos": [
    -- 	      {
    -- 	        "telefones": [
    -- 	          {
    -- 	            "numero": "1999655554",
    -- 	            "tipo": "T",
    -- 	            "id": 2555559
    -- 	          } 
    -- 	        ],
    -- 	        "cargo": "zzz de compras",
    -- 	        "nome": "Gerard",
    -- 	        "emails": [
    -- 	          {
    -- 	            "email": "gerard@terra.com.br",
    -- 	            "tipo": "T",
    -- 	            "id": 1065559
    -- 	          } 
    -- 	        ],
    -- 	        "id": 844485,
    -- 	        "excluido": false
    -- 	      } 
    -- 	    ],
    -- 	    "tipo": "J",
    -- 	    "razao_social": "TIDY TECNOLOGIA LTDA - EPP",
    -- 	    "nome_fantasia": "TIDY",
    -- 	    "bairro": "TUNA",
    -- 	    "cidade": "JAGUAR",
    -- 	    "inscricao_estadual": "395.222.441.222",
    -- 	    "observacao": "ligar sempre depois das 14hs",
    -- 	    "id": 2255597,
    -- 	    "ultima_alteracao": "2016-12-28 07:31:52",
    -- 	    "cep": "13555500",
    -- 	    "suframa": "",
    -- 	    "estado": "SP",
    -- 	    "emails": [
    -- 	      {
    -- 	        "email": "xi@tidy.com.br",
    -- 	        "tipo": "T",
    -- 	        "id": 10655558
    -- 	      } 
    -- 	    ],
    -- 	    "excluido": false
    -- 	  } 
    -- 	]
    -- 

    -- Construct a StringBuilder containing the above JSON array.
    DECLARE @sb int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @bCrlf int
    SELECT @bCrlf = 1
    DECLARE @success int
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '[', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '  {', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "telefones": [', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '      {', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "numero": "19995555555",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "tipo": "T",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "id": 2541437', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '      }', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    ],', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "cnpj": "11395551000164",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "rua": "R XAVIER AUGUSTO ROGGE, 22",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "complemento": "",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "contatos": [', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    ],', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "tipo": "J",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "razao_social": "SOUP BRASIL LTDA - ME",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "nome_fantasia": "SOUP BRASIL",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "bairro": "ABC DOS COLIBRIS",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "cidade": "TEST",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "inscricao_estadual": "222.102.222.116",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "observacao": "",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "id": 2209595,', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "ultima_alteracao": "2016-12-26 16:22:34",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "cep": "13555000",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "suframa": "",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "estado": "SP",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "emails": [', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '      {', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "email": "somebody@terra.com.br",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "tipo": "T",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "id": 1065557', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '      }', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    ],', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "excluido": false', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '  },', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '  {', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "telefones": [', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    ],', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "cnpj": "12496555500180",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "rua": "AV ROLF WIEST, 100",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "complemento": "ANDAR 7 SALA 612 A 620",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "contatos": [', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    ],', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "tipo": "J",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "razao_social": "SIMPLE SOFTWARE LTDA",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "nome_fantasia": "",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "bairro": "DOM ZETIRO",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "cidade": "APARTVILLE",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "inscricao_estadual": "",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "observacao": "",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "id": 2255594,', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "ultima_alteracao": "2016-12-26 16:28:31",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "cep": "89255505",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "suframa": "",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "estado": "SC",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "emails": [', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    ],', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "excluido": false', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '  },', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '  {', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "telefones": [', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '      {', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "numero": "1938655556",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "tipo": "T",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "id": 2555438', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '      }', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    ],', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "cnpj": "00003555500153",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "rua": "AV ABCDEF PINTO CATAO, 18",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "complemento": "",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "contatos": [', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '      {', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "telefones": [', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '          {', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '            "numero": "1999655554",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '            "tipo": "T",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '            "id": 2555559', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '          }', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        ],', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "cargo": "zzz de compras",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "nome": "Gerard",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "emails": [', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '          {', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '            "email": "gerard@terra.com.br",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '            "tipo": "T",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '            "id": 1065559', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '          }', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        ],', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "id": 844485,', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "excluido": false', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '      }', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    ],', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "tipo": "J",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "razao_social": "TIDY TECNOLOGIA LTDA - EPP",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "nome_fantasia": "TIDY",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "bairro": "TUNA",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "cidade": "JAGUAR",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "inscricao_estadual": "395.222.441.222",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "observacao": "ligar sempre depois das 14hs",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "id": 2255597,', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "ultima_alteracao": "2016-12-28 07:31:52",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "cep": "13555500",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "suframa": "",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "estado": "SP",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "emails": [', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '      {', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "email": "xi@tidy.com.br",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "tipo": "T",', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '        "id": 10655558', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '      }', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    ],', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '    "excluido": false', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '  }', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, ']', @bCrlf

    -- Load the JSON array into a JsonArray:
    DECLARE @jsonArray int
    -- Use "Chilkat_9_5_0.JsonArray" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @jsonArray OUT

    DECLARE @success int
    EXEC sp_OAMethod @jsonArray, 'LoadSb', @success OUT, @sb
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @jsonArray, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @jsonArray
        RETURN
      END

    -- Get some information from each record in the array.
    DECLARE @numRecords int
    EXEC sp_OAGetProperty @jsonArray, 'Size', @numRecords OUT
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numRecords
      BEGIN


        PRINT '------ Record ' + @i + ' -------'

        DECLARE @jsonRecord int
        EXEC sp_OAMethod @jsonArray, 'ObjectAt', @jsonRecord OUT, @i

        -- Examine information for this record
        DECLARE @numTelefones int
        EXEC sp_OAMethod @jsonRecord, 'SizeOfArray', @numTelefones OUT, 'telefones'

        PRINT 'Number of telefones: ' + @numTelefones
        DECLARE @j int
        SELECT @j = 0
        WHILE @j < @numTelefones
          BEGIN
            EXEC sp_OASetProperty @jsonRecord, 'J', @j

            EXEC sp_OAMethod @jsonRecord, 'StringOf', @sTmp0 OUT, 'telefones[j].numero'
            PRINT '  telefones numero: ' + @sTmp0

            EXEC sp_OAMethod @jsonRecord, 'StringOf', @sTmp0 OUT, 'telefones[j].tipo'
            PRINT '  telefones tipo: ' + @sTmp0

            EXEC sp_OAMethod @jsonRecord, 'StringOf', @sTmp0 OUT, 'telefones[j].id'
            PRINT '  telefones id: ' + @sTmp0
            SELECT @j = @j + 1
          END


        EXEC sp_OAMethod @jsonRecord, 'StringOf', @sTmp0 OUT, 'cnpj'
        PRINT 'cnpj: ' + @sTmp0

        EXEC sp_OAMethod @jsonRecord, 'StringOf', @sTmp0 OUT, 'rua'
        PRINT 'rua: ' + @sTmp0
        -- ...

        DECLARE @numContatos int
        EXEC sp_OAMethod @jsonRecord, 'SizeOfArray', @numContatos OUT, 'contatos'

        PRINT 'Number of contatos: ' + @numContatos
        SELECT @j = 0
        WHILE @j < @numContatos
          BEGIN
            EXEC sp_OASetProperty @jsonRecord, 'J', @j

            EXEC sp_OAMethod @jsonRecord, 'SizeOfArray', @numTelefones OUT, 'contatos[j].telefones'

            PRINT '  Number of telefones: ' + @numTelefones
            DECLARE @k int
            SELECT @k = 0
            WHILE @k < @numTelefones
              BEGIN
                EXEC sp_OASetProperty @jsonRecord, 'K', @k

                EXEC sp_OAMethod @jsonRecord, 'StringOf', @sTmp0 OUT, 'contatos[j].telefones[k].numero'
                PRINT '  telefones numero: ' + @sTmp0

                EXEC sp_OAMethod @jsonRecord, 'StringOf', @sTmp0 OUT, 'contatos[j].telefones[k].tipo'
                PRINT '  telefones tipo: ' + @sTmp0

                EXEC sp_OAMethod @jsonRecord, 'StringOf', @sTmp0 OUT, 'contatos[j].telefones[k].id'
                PRINT '  telefones id: ' + @sTmp0
                SELECT @k = @k + 1
              END


            EXEC sp_OAMethod @jsonRecord, 'StringOf', @sTmp0 OUT, 'contatos[j].cargo'
            PRINT '  cargo: ' + @sTmp0

            DECLARE @numEmails int
            EXEC sp_OAMethod @jsonRecord, 'SizeOfArray', @numEmails OUT, 'contatos[j].emails'

            PRINT '  Number of emails: ' + @numEmails
            SELECT @k = 0
            WHILE @k < @numEmails
              BEGIN
                EXEC sp_OASetProperty @jsonRecord, 'K', @k

                EXEC sp_OAMethod @jsonRecord, 'StringOf', @sTmp0 OUT, 'contatos[j].emails[k].email'
                PRINT '  emails email: ' + @sTmp0

                EXEC sp_OAMethod @jsonRecord, 'StringOf', @sTmp0 OUT, 'contatos[j].emails[k].tipo'
                PRINT '  emails tipo: ' + @sTmp0

                EXEC sp_OAMethod @jsonRecord, 'StringOf', @sTmp0 OUT, 'contatos[j].emails[k].id'
                PRINT '  emails id: ' + @sTmp0
                SELECT @k = @k + 1
              END

            SELECT @j = @j + 1
          END

        EXEC @hr = sp_OADestroy @jsonRecord

        SELECT @i = @i + 1
      END

    -- The output for the above code is:

    -- 	------ Record 0 -------
    -- 	Number of telefones: 1
    -- 	  telefones numero: 19995555555
    -- 	  telefones tipo: T
    -- 	  telefones id: 2541437
    -- 	cnpj: 11395551000164
    -- 	rua: R XAVIER AUGUSTO ROGGE, 22
    -- 	Number of contatos: 0
    -- 	------ Record 1 -------
    -- 	Number of telefones: 0
    -- 	cnpj: 12496555500180
    -- 	rua: AV ROLF WIEST, 100
    -- 	Number of contatos: 0
    -- 	------ Record 2 -------
    -- 	Number of telefones: 1
    -- 	  telefones numero: 1938655556
    -- 	  telefones tipo: T
    -- 	  telefones id: 2555438
    -- 	cnpj: 00003555500153
    -- 	rua: AV ABCDEF PINTO CATAO, 18
    -- 	Number of contatos: 1
    -- 	  Number of telefones: 1
    -- 	  telefones numero: 1999655554
    -- 	  telefones tipo: T
    -- 	  telefones id: 2555559
    -- 	  cargo: zzz de compras
    -- 	  Number of emails: 1
    -- 	  emails email: gerard@terra.com.br
    -- 	  emails tipo: T
    -- 	  emails id: 1065559
    -- 

    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @jsonArray


END
GO