none
Extrair dados Json (SQL) RRS feed

  • Pergunta

  • Bom dia,

    Vou extrair dados de algumas empresas pelo site da receita e encontrei essa query.

    Porém no json existem varias tags "nome" e gostaria de puxar sempre a primeira da lista, alguém poderia me ajudar?

    As informações que estão puxando hoje, sempre pega o primeiro nome em ordem alfabética e não do arquivo.

    Nesse caso gostaria de trazer o nome "SETORIAL" e não "BRUNO".


    DROP Table #JSON;
    
    DECLARE @OBJ INT;
    DECLARE @URL VARCHAR(200);
    DECLARE @RESPONSE VARCHAR(8000);
    DECLARE @CNPJ VARCHAR(14);
    DECLARE @DADOS NVARCHAR(MAX);
    
    CREATE TABLE #json (
    Ds_Dados VARCHAR(MAX)
    )
    
    SET @CNPJ = '62162458000102'
    SET @URL = 'https://www.receitaws.com.br/v1/cnpj/' + @CNPJ
    EXEC SP_OACREATE 'MSXML2.ServerXMLHttp', @OBJ out
    EXEC SP_OAMETHOD @OBJ, 'OPEN', NULL, 'GET', @URL, FALSE
    EXEC SP_OAMETHOD @OBJ, 'SEND'
    insert into #json
    exec SP_OAGETPROPERTY @OBJ, 'responseText'
    EXEC SP_OADESTROY @OBJ
    SELECT	@DADOS = Ds_Dados FROM #json
    --select * from #json
    
    SELECT * FROM dbo.fncJSON_Read (@DADOS)
    
    select *
    from (
        select
            ds_nome,ds_string
        FROM dbo.fncJSON_Read (@DADOS)
    )  em_linhas
    pivot (min(ds_string)  for ds_nome in ([Nome],[CNPJ],[Situacao],[Telefone])) em_colunas
    
    go

    Agradeço desde já

    sexta-feira, 17 de janeiro de 2020 13:47

Todas as Respostas

  • Bom dia,

    Vou extrair dados de algumas empresas pelo site da receita e encontrei essa query.

    Porém no json existem varias tags "nome" e gostaria de puxar sempre a primeira da lista, alguém poderia me ajudar?

    As informações que estão puxando hoje, sempre pega o primeiro nome em ordem alfabética e não do arquivo.

    Nesse caso gostaria de trazer o nome "SETORIAL" e não "BRUNO".


    DROP Table #JSON;
    
    DECLARE @OBJ INT;
    DECLARE @URL VARCHAR(200);
    DECLARE @RESPONSE VARCHAR(8000);
    DECLARE @CNPJ VARCHAR(14);
    DECLARE @DADOS NVARCHAR(MAX);
    
    CREATE TABLE #json (
    Ds_Dados VARCHAR(MAX)
    )
    
    SET @CNPJ = '62162458000102'
    SET @URL = 'https://www.receitaws.com.br/v1/cnpj/' + @CNPJ
    EXEC SP_OACREATE 'MSXML2.ServerXMLHttp', @OBJ out
    EXEC SP_OAMETHOD @OBJ, 'OPEN', NULL, 'GET', @URL, FALSE
    EXEC SP_OAMETHOD @OBJ, 'SEND'
    insert into #json
    exec SP_OAGETPROPERTY @OBJ, 'responseText'
    EXEC SP_OADESTROY @OBJ
    SELECT	@DADOS = Ds_Dados FROM #json
    --select * from #json
    
    SELECT * FROM dbo.fncJSON_Read (@DADOS)
    
    select *
    from (
        select
            ds_nome,ds_string
        FROM dbo.fncJSON_Read (@DADOS)
    )  em_linhas
    pivot (min(ds_string)  for ds_nome in ([Nome],[CNPJ],[Situacao],[Telefone])) em_colunas
    
    go

    Agradeço desde já

    Estou utilizando a Function:

    CREATE FUNCTION dbo.fncJSON_Read (     
        @JSON NVARCHAR(MAX)     
    )    
    RETURNS @Retorno TABLE (    
        Id_Elemento INT NULL,    
        Nr_Sequencia [INT] NULL,    
        Id_Objeto_Pai INT,    
        Id_Objeto INT,    
        Ds_Nome NVARCHAR(2000),    
        Ds_String NVARCHAR(MAX) NOT NULL,    
        Ds_Tipo VARCHAR(10) NOT NULL    
    )    
    AS    
    BEGIN    
        
        
        DECLARE    
            @FirstObject INT,    
            @OpenDelimiter INT,    
            @NextOpenDelimiter INT,    
            @NextCloseDelimiter INT,    
            @Type NVARCHAR(10),    
            @NextCloseDelimiterChar CHAR(1),    
            @Contents NVARCHAR(MAX),    
            @Start INT,    
            @end INT,    
            @param INT,    
            @EndOfDs_Nome INT,    
            @token NVARCHAR(200),    
            @value NVARCHAR(MAX),    
            @Nr_Sequencia INT,    
            @Ds_Nome NVARCHAR(200),    
            @Id_Objeto_Pai INT,    
            @lenJSON INT,    
            @characters NCHAR(36),    
            @result BIGINT,    
            @index SMALLINT,    
            @Escape INT    
           
         
        DECLARE @Strings TABLE (    
            String_ID INT IDENTITY(1, 1),    
            Ds_String NVARCHAR(MAX)    
        )    
            
        SELECT    
            @characters = '0123456789abcdefghijklmnopqrstuvwxyz',    
            @Nr_Sequencia = 0,    
            @Id_Objeto_Pai = 0;    
                
        WHILE (1 = 1)    
        BEGIN    
            
            SELECT @Start = PATINDEX('%[^a-zA-Z]["]%', @JSON COLLATE SQL_Latin1_General_CP850_BIN);    
                
            IF (@Start = 0)    
                BREAK    
                    
            IF (SUBSTRING(@JSON, @Start + 1, 1) = '"')    
            BEGIN    
                SET @Start = @Start + 1;    
                SET @end = PATINDEX('%[^\]["]%', RIGHT(@JSON, LEN(@JSON + '|') - @Start) COLLATE SQL_Latin1_General_CP850_BIN);    
            END    
                
            IF (@end = 0)    
                BREAK    
                    
            SELECT    
                @token = SUBSTRING(@JSON, @Start + 1, @end - 1)    
              
              
            SELECT    
                @token = REPLACE(@token, FromString, ToString)    
            FROM (    
                  SELECT '\"' AS FromString, '"' AS ToString    
                  UNION ALL    
                  SELECT '\\', '\'    
                  UNION ALL    
                  SELECT '\/', '/'    
                  UNION ALL    
                  SELECT '\b', CHAR(08)    
                  UNION ALL    
                  SELECT '\f', CHAR(12)    
                  UNION ALL    
                  SELECT '\n', CHAR(10)    
                  UNION ALL    
                  SELECT '\r', CHAR(13)    
                  UNION ALL    
                  SELECT '\t', CHAR(09)    
            ) substitutions    
                
                
            SELECT    
                @result = 0,    
                @Escape = 1    
          
          
            WHILE (@Escape > 0)    
            BEGIN    
                
                SELECT    
                    @index = 0,    
                    @Escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token COLLATE SQL_Latin1_General_CP850_BIN)    
                        
                        
                IF (@Escape > 0)    
                BEGIN    
                    
                    WHILE (@index < 4)    
                    BEGIN    
                        
                        SELECT    
                            @result = @result + POWER(16, @index) * ( CHARINDEX(SUBSTRING(@token, @Escape + 2 + 3 - @index, 1), @characters) - 1 ),    
                            @index = @index + 1;    
        
                    END    
                              
                    SELECT @token = STUFF(@token, @Escape, 6, NCHAR(@result))    
                        
                END    
                    
            END    
              
              
            INSERT INTO @Strings ( Ds_String )    
            SELECT @token    
              
                
            SELECT @JSON = STUFF(@JSON, @Start, @end + 1, '@string' + CONVERT(NVARCHAR(5), @@IDENTITY))    
                
        END    
          
          
        WHILE (1 = 1)    
        BEGIN    
         
            SELECT @Id_Objeto_Pai = @Id_Objeto_Pai + 1    
            SELECT @FirstObject = PATINDEX('%[{[[]%', @JSON COLLATE SQL_Latin1_General_CP850_BIN)    
                
            IF (@FirstObject = 0)    
                BREAK    
                    
                    
            IF ( SUBSTRING(@JSON, @FirstObject, 1) = '{' )    
                SELECT @NextCloseDelimiterChar = '}', @Type = 'object'    
            ELSE    
                SELECT @NextCloseDelimiterChar = ']', @Type = 'array'    
           
                     
            SELECT @OpenDelimiter = @FirstObject    
         
         
            WHILE (1 = 1)    
            BEGIN    
                
                SELECT @lenJSON = LEN(@JSON + '|') - 1    
                SELECT @NextCloseDelimiter = CHARINDEX(@NextCloseDelimiterChar, @JSON, @OpenDelimiter + 1)    
                SELECT @NextOpenDelimiter = PATINDEX('%[{[[]%', RIGHT(@JSON, @lenJSON - @OpenDelimiter) COLLATE SQL_Latin1_General_CP850_BIN)    
                    
                    
                IF (@NextOpenDelimiter = 0)    
                    BREAK    
                      
                        
                SELECT @NextOpenDelimiter = @NextOpenDelimiter + @OpenDelimiter    
                    
                    
                IF (@NextCloseDelimiter < @NextOpenDelimiter)    
                    BREAK    
                        
                    
                IF SUBSTRING(@JSON, @NextOpenDelimiter, 1) = '{'    
                    SELECT @NextCloseDelimiterChar = '}', @Type = 'object'    
                ELSE    
                    SELECT @NextCloseDelimiterChar = ']', @Type = 'array'    
        
                    
                SELECT @OpenDelimiter = @NextOpenDelimiter    
                    
                    
            END    
                
                
            SELECT @Contents = SUBSTRING(@JSON, @OpenDelimiter + 1, @NextCloseDelimiter - @OpenDelimiter - 1)    
            SELECT @JSON = STUFF(@JSON, @OpenDelimiter, @NextCloseDelimiter - @OpenDelimiter + 1, '@' + @Type + CONVERT(NVARCHAR(5), @Id_Objeto_Pai))    
                
                
            WHILE (( PATINDEX('%[A-Za-z0-9@+.e]%', @Contents COLLATE SQL_Latin1_General_CP850_BIN) ) <> 0)    
            BEGIN    
                
                IF (@Type = 'Object')    
                BEGIN    
                            
                    SELECT     
                        @Nr_Sequencia = 0,    
                        @end = CHARINDEX(':', ' ' + @Contents)    
                            
                            
                    SELECT @Start = PATINDEX('%[^A-Za-z@][@]%', ' ' + @Contents COLLATE SQL_Latin1_General_CP850_BIN)--AAAAAAAA    
                            
                              
                    SELECT    
                        @token = SUBSTRING(' ' + @Contents, @Start + 1, @end - @Start - 1),    
                        @EndOfDs_Nome = PATINDEX('%[0-9]%', @token COLLATE SQL_Latin1_General_CP850_BIN),    
                        @param = RIGHT(@token, LEN(@token) - @EndOfDs_Nome + 1)    
                            
                            
                    SELECT    
                        @token = LEFT(@token, @EndOfDs_Nome - 1),    
                        @Contents = RIGHT(' ' + @Contents, LEN(' ' + @Contents + '|') - @end - 1)    
                            
                            
                    SELECT    
                        @Ds_Nome = Ds_String    
                    FROM    
                        @Strings    
                    WHERE    
                        String_ID = @param    
                            
                            
                END    
                ELSE    
                    SELECT     
                        @Ds_Nome = NULL,     
                        @Nr_Sequencia = @Nr_Sequencia + 1    
                            
                            
                SELECT @end = CHARINDEX(',', @Contents)    
                    
                    
                IF (@end = 0)    
                    SELECT @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents + ' ' COLLATE SQL_Latin1_General_CP850_BIN) + 1    
                        
                        
                SELECT @Start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' ' + @Contents COLLATE SQL_Latin1_General_CP850_BIN)    
              
              
                SELECT    
                    @value = RTRIM(SUBSTRING(@Contents, @Start, @end - @Start)),    
                    @Contents = RIGHT(@Contents + ' ', LEN(@Contents + '|') - @end)    
                     
                        
                IF (SUBSTRING(@value, 1, 7) = '@object')    
                BEGIN    
                    
                    INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Id_Objeto, Ds_Tipo )    
                    SELECT    
                        @Ds_Nome,    
                        @Nr_Sequencia,    
                        @Id_Objeto_Pai,    
                        SUBSTRING(@value, 8, 5),    
                        SUBSTRING(@value, 8, 5),    
                        'object'    
                            
                END    
                ELSE BEGIN    
                    
                    IF (SUBSTRING(@value, 1, 6) = '@array')    
                        
                        INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Id_Objeto, Ds_Tipo )    
                        SELECT    
                            @Ds_Nome,    
                            @Nr_Sequencia,    
                            @Id_Objeto_Pai,    
                            SUBSTRING(@value, 7, 5),    
                            SUBSTRING(@value, 7, 5),    
                            'array'    
                                
                    ELSE    
                        
                        IF (SUBSTRING(@value, 1, 7) = '@string')    
                            INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Ds_Tipo )    
                            SELECT    
                                @Ds_Nome,    
                                @Nr_Sequencia,    
                                @Id_Objeto_Pai,    
                                Ds_String,    
                                'string'    
                             FROM    
                                @Strings    
                             WHERE    
                                String_ID = SUBSTRING(@value, 8, 5)    
                                    
                        ELSE    
                            
                             IF (@value IN ( 'true', 'false' ))    
                                INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Ds_Tipo )    
                                SELECT    
                                    @Ds_Nome,    
                                    @Nr_Sequencia,    
                                    @Id_Objeto_Pai,    
                                    @value,    
                                    'boolean'    
                             ELSE    
                                 
                                IF (@value = 'null')    
                                    INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Ds_Tipo )    
                                    SELECT    
                                        @Ds_Nome,    
                                        @Nr_Sequencia,    
                                        @Id_Objeto_Pai,    
                                        @value,    
                                        'null'    
                                            
                                ELSE    
                                    
                                    IF (PATINDEX('%[^0-9]%', @value COLLATE SQL_Latin1_General_CP850_BIN) > 0)    
                                        INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Ds_Tipo )    
                                        SELECT    
                                            @Ds_Nome,    
                                            @Nr_Sequencia,    
                                            @Id_Objeto_Pai,    
                                            @value,    
                                            'real'    
                                                
                                    ELSE    
                                        
                                        INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Ds_Tipo )    
                                        SELECT    
                                            @Ds_Nome,    
                                            @Nr_Sequencia,    
                                            @Id_Objeto_Pai,    
                                            @value,    
          'int'    
                                                
                    IF (@Contents = ' ')    
                        SELECT @Nr_Sequencia = 0    
                            
                END    
                    
            END    
                
        END    
                
                
        INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Id_Objeto, Ds_Tipo )    
        SELECT    
            '-',    
            1,    
            NULL,    
            '',    
            @Id_Objeto_Pai - 1,    
            @Type    
                
                
            
        DECLARE @Tabela_Final TABLE (    
            Id_Elemento INT IDENTITY(1, 1) NOT NULL,    
            Nr_Sequencia [INT] NULL,    
            Id_Objeto_Pai INT,    
            Id_Objeto INT,    
            Ds_Nome NVARCHAR(2000),    
            Ds_String NVARCHAR(MAX) NOT NULL,    
            Ds_Tipo VARCHAR(10) NOT NULL    
        )    
            
        INSERT INTO @Tabela_Final    
        SELECT     
            Nr_Sequencia,    
            Id_Objeto_Pai,    
            Id_Objeto,    
            Ds_Nome,    
            Ds_String,    
            Ds_Tipo     
        FROM    
            @Retorno    
        ORDER BY     
            ISNULL(Id_Objeto, Id_Objeto_Pai) DESC,    
            Id_Objeto_Pai DESC,    
            Id_Elemento    
                
                
        DELETE FROM @Retorno    
            
            
        INSERT INTO @Retorno    
        SELECT    
            Id_Elemento,    
            Nr_Sequencia,    
            Id_Objeto_Pai,    
            Id_Objeto,    
            Ds_Nome,    
            Ds_String,    
            Ds_Tipo     
        FROM     
            @Tabela_Final    
            
                
        RETURN    
            
            
    END


    sexta-feira, 17 de janeiro de 2020 13:51
  • Deleted
    sexta-feira, 17 de janeiro de 2020 14:49
  • Versão 2008

    Então José, eu consigo caminhar pelo objeto pai, porém cada CNPJ possui as posições diferentes.

    Precisaria de um padrão para sempre puxar as mesmas informações das empresas.


    sexta-feira, 17 de janeiro de 2020 15:07
  • Deleted
    sexta-feira, 17 de janeiro de 2020 15:54
  • José fiz os testes aqui, adicionei algumas colunas, pesquisei vários CNPJs e todos puxaram certinho a Razão Social.

    Você é o cara!!

    Muito obrigado mais uma vez!!

    sexta-feira, 17 de janeiro de 2020 17:29
  • Deleted
    sexta-feira, 17 de janeiro de 2020 17:46
  • Ta certo, obrigado pela ajuda!!!
    sexta-feira, 17 de janeiro de 2020 20:19