SQL Server
SQL Server
Loading and Parsing a Complex JSON Array
See more JSON Examples
This example loads a JSON array containing more complex data. It shows how to parse (access) various values contained within the JSON.Chilkat SQL Server Downloads
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- 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
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
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
EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @jsonArray OUT
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