Como utilizar JSON no SQL Server 2008, 2012 e 2014 - Lendo strings JSON, importando para o banco e exportando para XML

Visualizações: 15.209 views

Neste artigo

Tempo de Leitura: 10 minutos

Olá Pessoal,
Boa tarde.

Neste post irei demonstrar como ler strings JSON e converter para uma tabela e converter os dados de uma tabela para JSON e XML utilizando apenas T-SQL. Essa dica é bem útil quando você está utilizando uma versão anterior ao SQL Server 2016 e precisa ler strings Json.

Caso você esteja utilizando o SQL Server 2016 ou superior, saiba que já existe suporte nativo à JSON. Para saber como funciona esse suporte nativo, leia o meu post SQL Server 2016 – Utilizando o suporte nativo a JSON (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY).

O que é JSON

JSON, um acrônimo para “JavaScript Object Notation”, é um formato leve para intercâmbio de dados computacionais. A simplicidade de JSON tem resultado em seu uso difundido, especialmente como uma alternativa para XML em AJAX.

Uma das vantagens reivindicadas de JSON sobre XML como um formato para intercâmbio de dados neste contexto, é o fato de ser muito mais fácil escrever um analisador JSON. Em JavaScript mesmo, JSON pode ser analisado trivialmente usando a função eval(). Isto foi importante para a aceitação de JSON dentro da comunidade AJAX devido a presença deste recurso de JavaScript em todos os navegadores web atuais.

Na prática, os argumentos a respeito da facilidade de desenvolvimento e desempenho do analisador são raramente relevados devido aos interesses de segurança no uso de eval() e a crescente integração de processamento XML nos navegadores web modernos. Por esta razão JSON é tipicamente usado em ambientes onde o tamanho do fluxo de dados entre o cliente e o servidor é de supra importância (daí seu uso por Google, Yahoo, etc., os quais servem milhões de usuários), onde a fonte dos dados pode ser explicitamente confiável, e onde a perda dos recursos de processamento XSLT no lado cliente para manipulação de dados ou geração da interface, não é uma consideração.

Um ótimo uso para a utilização de JSON no SQL Server e o que me motivou a fazer este post, é para a integração e consumo de dados de webservices que utilizam JSON para estabelecer a comunicação com o cliente consumidor da informação.

Lendo uma string JSON

Com a utilização da função que vou postar abaixo, criada pelo Phil Factor, é possível ler strings JSON facilmente, conforme o exemplo abaixo:

Implementando a função:
Visualizar código-fonte

IF (OBJECT_ID (N'dbo.fncJSON_Read') IS NOT NULL) DROP FUNCTION dbo.fncJSON_Read
GO

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

IF (OBJECT_ID (N'dbo.fncJSON_Read') IS NOT NULL) DROP FUNCTION dbo.fncJSON_Read

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

)

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

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),

'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),

'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

INSERT INTO @Retorno ( Ds_Nome, Nr_Sequencia, Id_Objeto_Pai, Ds_String, Id_Objeto, Ds_Tipo )

SELECT

'-',

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

Criando uma string JSON a partir do banco

Como o uso dessa função, você pode criar uma string JSON a partir de uma tabela em um formato específico. Para isso, precisamos criar esse tipo de dado antes de utilizar a nossa função.

Pré-Requisito – Criando a função para tratar os dados:

IF (OBJECT_ID (N'dbo.fncJSON_Escape') IS NOT NULL) DROP FUNCTION dbo.fncJSON_Escape
GO

CREATE FUNCTION dbo.fncJSON_Escape ( 
    @Ds_String NVARCHAR(MAX) 
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

    SELECT
        @Ds_String = REPLACE(@Ds_String, FromString, ToString)
    FROM
        (
          SELECT '\"' AS FromString, '"' AS ToString
          UNION ALL
          SELECT '\', '\\'
          UNION ALL
          SELECT '/', '\/'
          UNION ALL
          SELECT CHAR(08), '\b'
          UNION ALL
          SELECT CHAR(12), '\f'
          UNION ALL
          SELECT CHAR(10), '\n'
          UNION ALL
          SELECT CHAR(13), '\r'
          UNION ALL
          SELECT CHAR(09), '\t'
        ) substitutions

    RETURN @Ds_String

END

IF (OBJECT_ID (N'dbo.fncJSON_Escape') IS NOT NULL) DROP FUNCTION dbo.fncJSON_Escape

CREATE FUNCTION dbo.fncJSON_Escape (

@Ds_String NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

BEGIN

SELECT

@Ds_String = REPLACE(@Ds_String, FromString, ToString)

FROM

(

SELECT '\"' AS FromString, '"' AS ToString

UNION ALL

SELECT '\', '\\'

UNION ALL

SELECT '/', '\/'

UNION ALL

SELECT CHAR(08), '\b'

UNION ALL

SELECT CHAR(12), '\f'

UNION ALL

SELECT CHAR(10), '\n'

UNION ALL

SELECT CHAR(13), '\r'

UNION ALL

SELECT CHAR(09), '\t'

) substitutions

RETURN @Ds_String

END

Pré-Requisito – Criando o tipo JSON:

IF EXISTS (SELECT * FROM sys.types WHERE name = 'JSON') DROP TYPE dbo.JSON
CREATE TYPE dbo.JSON AS TABLE (
    Id_Elemento INT 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,
    PRIMARY KEY (Id_Elemento)
)

IF EXISTS (SELECT * FROM sys.types WHERE name = 'JSON') DROP TYPE dbo.JSON

CREATE TYPE dbo.JSON AS TABLE (

Id_Elemento INT 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,

PRIMARY KEY (Id_Elemento)

)

Implementando a função:
Visualizar código-fonte

IF (OBJECT_ID (N'dbo.fncJSON_From_SQL') IS NOT NULL) DROP FUNCTION dbo.fncJSON_From_SQL
GO

CREATE FUNCTION dbo.fncJSON_From_SQL (
    @Tabela_Original JSON READONLY
)
RETURNS NVARCHAR(MAX)
AS
BEGIN


    DECLARE
        @JSON NVARCHAR(MAX),
        @NewJSON NVARCHAR(MAX),
        @Where INT,
        @ANumber INT,
        @notNumber INT,
        @indent INT,
        @ii INT,
        @CrLf CHAR(2)

      
    SELECT
        @CrLf = CHAR(13) + CHAR(10),
        @JSON = CASE Ds_Tipo
                  WHEN 'array' THEN +COALESCE('{' + @CrLf + '  "' + Ds_Nome + '" : ', '') + '['
                  ELSE '{'
                END + @CrLf + CASE WHEN Ds_Tipo = 'array' AND Ds_Nome IS NOT NULL THEN '  '
                                   ELSE ''
                              END + '@Object' + CONVERT(VARCHAR(5), Id_Objeto) + @CrLf + CASE Ds_Tipo
                                                                                           WHEN 'array' THEN CASE WHEN Ds_Nome IS NULL THEN ']'
                                                                                                                  ELSE '  ]' + @CrLf + '}' + @CrLf
                                                                                                             END
                                                                                           ELSE '}'
                                                                                         END
    FROM
        @Tabela_Original
    WHERE
        Id_Objeto_Pai IS NULL AND Ds_Tipo IN ( 'object', 'document', 'array' )


    SELECT
        @ii = 1000

    WHILE (@ii > 0)
    BEGIN

        SELECT
            @Where = PATINDEX('%[^[a-zA-Z0-9]@Object%', @JSON)

        IF @Where = 0
            BREAK

    
        SET @indent = CHARINDEX(CHAR(10) + CHAR(13), REVERSE(LEFT(@JSON, @Where)) + CHAR(10) + CHAR(13)) - 1
        SET @notNumber = PATINDEX('%[^0-9]%', RIGHT(@JSON, LEN(@JSON + '|') - @Where - 8) + ' ')
        SET @NewJSON = NULL

        SELECT
            @NewJSON = COALESCE(@NewJSON + ',' + @CrLf + SPACE(@indent), '') + CASE WHEN parent.Ds_Tipo = 'array' THEN ''
                                                                                    ELSE COALESCE('"' + TheRow.Ds_Nome + '" : ', '')
                                                                               END + CASE TheRow.Ds_Tipo
                                                                                       WHEN 'array' THEN '  [' + @CrLf + SPACE(@indent + 2) + '@Object' + CONVERT(VARCHAR(5), TheRow.[Id_Objeto]) + @CrLf + SPACE(@indent + 2) + ']'
                                                                                       WHEN 'object' THEN '  {' + @CrLf + SPACE(@indent + 2) + '@Object' + CONVERT(VARCHAR(5), TheRow.[Id_Objeto]) + @CrLf + SPACE(@indent + 2) + '}'
                                                                                       WHEN 'string' THEN '"' + dbo.fncJSON_Escape(TheRow.Ds_String) + '"'
                                                                                       ELSE TheRow.Ds_String
                                                                                     END
        FROM
            @Tabela_Original TheRow
            INNER JOIN @Tabela_Original Parent ON parent.Id_Elemento = TheRow.Id_Objeto_Pai
        WHERE
            TheRow.Id_Objeto_Pai = SUBSTRING(@JSON, @Where + 8, @notNumber - 1)


        SELECT
            @JSON = STUFF(@JSON, @Where + 1, 8 + @notNumber - 1, @NewJSON),
            @ii = @ii - 1

    END


    RETURN @JSON


END
GO

IF (OBJECT_ID (N'dbo.fncJSON_From_SQL') IS NOT NULL) DROP FUNCTION dbo.fncJSON_From_SQL

CREATE FUNCTION dbo.fncJSON_From_SQL (

@Tabela_Original JSON READONLY

)

RETURNS NVARCHAR(MAX)

BEGIN

DECLARE

@JSON NVARCHAR(MAX),

@NewJSON NVARCHAR(MAX),

@Where INT,

@ANumber INT,

@notNumber INT,

@indent INT,

@ii INT,

@CrLf CHAR(2)

SELECT

@CrLf = CHAR(13) + CHAR(10),

@JSON = CASE Ds_Tipo

WHEN 'array' THEN +COALESCE('{' + @CrLf + ' "' + Ds_Nome + '" : ', '') + '['

ELSE '{'

END + @CrLf + CASE WHEN Ds_Tipo = 'array' AND Ds_Nome IS NOT NULL THEN ' '

ELSE ''

END + '@Object' + CONVERT(VARCHAR(5), Id_Objeto) + @CrLf + CASE Ds_Tipo

WHEN 'array' THEN CASE WHEN Ds_Nome IS NULL THEN ']'

ELSE ' ]' + @CrLf + '}' + @CrLf

END

ELSE '}'

END

FROM

@Tabela_Original

WHERE

Id_Objeto_Pai IS NULL AND Ds_Tipo IN ( 'object', 'document', 'array' )

SELECT

@ii = 1000

WHILE (@ii > 0)

BEGIN

SELECT

@Where = PATINDEX('%[^[a-zA-Z0-9]@Object%', @JSON)

IF @Where = 0

BREAK

SET @indent = CHARINDEX(CHAR(10) + CHAR(13), REVERSE(LEFT(@JSON, @Where)) + CHAR(10) + CHAR(13)) - 1

SET @notNumber = PATINDEX('%[^0-9]%', RIGHT(@JSON, LEN(@JSON + '|') - @Where - 8) + ' ')

SET @NewJSON = NULL

SELECT

@NewJSON = COALESCE(@NewJSON + ',' + @CrLf + SPACE(@indent), '') + CASE WHEN parent.Ds_Tipo = 'array' THEN ''

ELSE COALESCE('"' + TheRow.Ds_Nome + '" : ', '')

END + CASE TheRow.Ds_Tipo

WHEN 'array' THEN ' [' + @CrLf + SPACE(@indent + 2) + '@Object' + CONVERT(VARCHAR(5), TheRow.[Id_Objeto]) + @CrLf + SPACE(@indent + 2) + ']'

WHEN 'object' THEN ' {' + @CrLf + SPACE(@indent + 2) + '@Object' + CONVERT(VARCHAR(5), TheRow.[Id_Objeto]) + @CrLf + SPACE(@indent + 2) + '}'

WHEN 'string' THEN '"' + dbo.fncJSON_Escape(TheRow.Ds_String) + '"'

ELSE TheRow.Ds_String

END

FROM

@Tabela_Original TheRow

INNER JOIN @Tabela_Original Parent ON parent.Id_Elemento = TheRow.Id_Objeto_Pai

WHERE

TheRow.Id_Objeto_Pai = SUBSTRING(@JSON, @Where + 8, @notNumber - 1)

SELECT

@JSON = STUFF(@JSON, @Where + 1, 8 + @notNumber - 1, @NewJSON),

@ii = @ii - 1

END

RETURN @JSON

END

Criando um XML a partir de um JSON

Com o uso da função fncJSON_To_XML, pode-se criar um XML a partir de uma string JSON passada para a função, assim com o exemplo abaixo:

Caso você necessite de ajuda para ler e manipular dados em objetos XML utilizando o SQL Server, aprenda como fazer isso lendo o meu artigo SQL Server – Como ler, importar e exportar dados de arquivos XML.

Implementando a função:
Visualizar código-fonte

IF (OBJECT_ID(N'dbo.fncJSON_To_XML') IS NOT NULL) DROP FUNCTION dbo.fncJSON_To_XML
GO

CREATE FUNCTION dbo.fncJSON_To_XML (
    @Tabela_Original JSON READONLY
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

    DECLARE
        @XMLAsString NVARCHAR(MAX),
        @NewXML NVARCHAR(MAX),
        @Entities NVARCHAR(MAX),
        @Objects NVARCHAR(MAX),
        @Ds_Nome NVARCHAR(200),
        @Where INT,
        @ANumber INT,
        @notNumber INT,
        @indent INT,
        @CrLf CHAR(2)

      
    SELECT
        @CrLf = CHAR(13) + CHAR(10),
        @XMLAsString = '<?xml version="1.0" ?>

@Object' + CONVERT(VARCHAR(5), Id_Objeto) + '

'
    FROM
        @Tabela_Original
    WHERE
        Id_Objeto_Pai IS NULL AND Ds_Tipo IN ( 'object', 'array' )


    WHILE (1 = 1)
    BEGIN

        SELECT
            @Where = PATINDEX('%[^a-zA-Z0-9]@Object%', @XMLAsString)

        IF (@Where = 0)
            BREAK

    
        SET @indent = CHARINDEX(CHAR(10) + CHAR(13), REVERSE(LEFT(@XMLAsString, @Where)) + CHAR(10) + CHAR(13)) - 1
        SET @notNumber = PATINDEX('%[^0-9]%', RIGHT(@XMLAsString, LEN(@XMLAsString + '|') - @Where - 8) + ' ')
        SET @Entities = NULL


        SELECT
            @Entities = COALESCE(@Entities + ' ', ' ') + Ds_Nome + '="' + REPLACE(REPLACE(REPLACE(Ds_String, '<', '&lt;'), '&', '&amp;'), '>', '&gt;') + '"'
        FROM
            @Tabela_Original
        WHERE
            Id_Objeto_Pai = SUBSTRING(@XMLAsString, @Where + 8, @notNumber - 1) 
            AND Ds_Tipo NOT IN ( 'array', 'object' )


        SELECT
            @Entities = COALESCE(@Entities, ''),
            @Objects = '',
            @Ds_Nome = CASE WHEN Ds_Nome = '-' THEN 'root'
                         ELSE Ds_Nome
                    END
        FROM
            @Tabela_Original
        WHERE
            [Id_Objeto] = SUBSTRING(@XMLAsString, @Where + 8, @notNumber - 1) 

    

        SELECT
            @Objects = @Objects + @CrLf + SPACE(@indent + 2) + '@Object' + CONVERT(VARCHAR(5), Id_Objeto)
        FROM
            @Tabela_Original
        WHERE
            Id_Objeto_Pai = SUBSTRING(@XMLAsString, @Where + 8, @notNumber - 1) 
            AND Ds_Tipo IN ( 'array', 'object' )


        IF (@Objects = '')
            SELECT @NewXML = '<' + COALESCE(@Ds_Nome, 'item') + @Entities + ' />'
        ELSE
            SELECT @NewXML = '<' + COALESCE(@Ds_Nome, 'item') + @Entities + '>' + @Objects + @CrLf + +SPACE(@indent) + '</' + COALESCE(@Ds_Nome, 'item') + '>'


        SELECT
            @XMLAsString = STUFF(@XMLAsString, @Where + 1, 8 + @notNumber - 1, @NewXML)


    END


    RETURN @XMLAsString


END

IF (OBJECT_ID(N'dbo.fncJSON_To_XML') IS NOT NULL) DROP FUNCTION dbo.fncJSON_To_XML

CREATE FUNCTION dbo.fncJSON_To_XML (

@Tabela_Original JSON READONLY

)

RETURNS NVARCHAR(MAX)

BEGIN

DECLARE

@XMLAsString NVARCHAR(MAX),

@NewXML NVARCHAR(MAX),

@Entities NVARCHAR(MAX),

@Objects NVARCHAR(MAX),

@Ds_Nome NVARCHAR(200),

@Where INT,

@ANumber INT,

@notNumber INT,

@indent INT,

@CrLf CHAR(2)

SELECT

@CrLf = CHAR(13) + CHAR(10),

@XMLAsString = '<?xml version="1.0" ?>

@Object' + CONVERT(VARCHAR(5), Id_Objeto) + '

FROM

@Tabela_Original

WHERE

Id_Objeto_Pai IS NULL AND Ds_Tipo IN ( 'object', 'array' )

WHILE (1 = 1)

BEGIN

SELECT

@Where = PATINDEX('%[^a-zA-Z0-9]@Object%', @XMLAsString)

IF (@Where = 0)

BREAK

SET @indent = CHARINDEX(CHAR(10) + CHAR(13), REVERSE(LEFT(@XMLAsString, @Where)) + CHAR(10) + CHAR(13)) - 1

SET @notNumber = PATINDEX('%[^0-9]%', RIGHT(@XMLAsString, LEN(@XMLAsString + '|') - @Where - 8) + ' ')

SET @Entities = NULL

SELECT

@Entities = COALESCE(@Entities + ' ', ' ') + Ds_Nome + '="' + REPLACE(REPLACE(REPLACE(Ds_String, '<', '<'), '&', '&'), '>', '>') + '"'

FROM

@Tabela_Original

WHERE

Id_Objeto_Pai = SUBSTRING(@XMLAsString, @Where + 8, @notNumber - 1)

AND Ds_Tipo NOT IN ( 'array', 'object' )

SELECT

@Entities = COALESCE(@Entities, ''),

@Objects = '',

@Ds_Nome = CASE WHEN Ds_Nome = '-' THEN 'root'

ELSE Ds_Nome

END

FROM

@Tabela_Original

WHERE

[Id_Objeto] = SUBSTRING(@XMLAsString, @Where + 8, @notNumber - 1)

SELECT

@Objects = @Objects + @CrLf + SPACE(@indent + 2) + '@Object' + CONVERT(VARCHAR(5), Id_Objeto)

FROM

@Tabela_Original

WHERE

Id_Objeto_Pai = SUBSTRING(@XMLAsString, @Where + 8, @notNumber - 1)

AND Ds_Tipo IN ( 'array', 'object' )

IF (@Objects = '')

SELECT @NewXML = '<' + COALESCE(@Ds_Nome, 'item') + @Entities + ' />'

ELSE

SELECT @NewXML = '<' + COALESCE(@Ds_Nome, 'item') + @Entities + '>' + @Objects + @CrLf + +SPACE(@indent) + '</' + COALESCE(@Ds_Nome, 'item') + '>'

SELECT

@XMLAsString = STUFF(@XMLAsString, @Where + 1, 8 + @notNumber - 1, @NewXML)

END

RETURN @XMLAsString

END

Solução alternativa para converter uma string JSON para XML

Com a função abaixo (escrita pelo Daniel Muchmacher), você poderá facilmente converter strings Json para um documento XML, onde você poderá utilizar o SQL Server para navegador entre os nós e ler os dados conforme sua necessidade. Essa função é muito útil para instâncias que estão utilizando uma versão anterior ao SQL Server 2016 (sem suporte nativo a JSON), mas você precisa utilizar esse recurso.

Exemplo:

DECLARE @json VARCHAR(MAX) = '{
    "glossary": {
        "title": "example glossary",
		"GlossDiv": {
            "title": "S",
			"GlossList": {
                "GlossEntry": {
                    "ID": "SGML",
					"SortAs": "SGML",
					"GlossTerm": "Standard Generalized Markup Language",
					"Acronym": "SGML",
					"Abbrev": "ISO 8879:1986",
					"GlossDef": {
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
						"GlossSeeAlso": ["GML", "XML"]
                    },
					"GlossSee": "markup"
                }
            }
        }
    }
}'

SELECT dbo.fncJson2xml(@json)

DECLARE @json VARCHAR(MAX) = '{

"glossary": {

"title": "example glossary",

"GlossDiv": {

"title": "S",

"GlossList": {

"GlossEntry": {

"ID": "SGML",

"SortAs": "SGML",

"GlossTerm": "Standard Generalized Markup Language",

"Acronym": "SGML",

"Abbrev": "ISO 8879:1986",

"GlossDef": {

"para": "A meta-markup language, used to create markup languages such as DocBook.",

"GlossSeeAlso": ["GML", "XML"]

"GlossSee": "markup"

}

SELECT dbo.fncJson2xml(@json)

Resultado:

Código-fonte da função

CREATE FUNCTION dbo.fncJson2xml (
    @json VARCHAR(MAX)
)
RETURNS XML
AS
BEGIN

    DECLARE
        @output VARCHAR(MAX),
        @key VARCHAR(MAX),
        @value VARCHAR(MAX),
        @recursion_counter INT,
        @offset INT,
        @nested BIT,
        @array BIT,
        @tab CHAR(1) = CHAR(9),
        @cr CHAR(1) = CHAR(13),
        @lf CHAR(1) = CHAR(10);

    SET @json=LTRIM(RTRIM( REPLACE(REPLACE(REPLACE(@json, @cr, ''), @lf, ''), @tab, '')));

    IF (LEFT(@json, 1)<>'{' OR RIGHT(@json, 1)<>'}')
        RETURN '';

    SET @json=LTRIM(RTRIM(SUBSTRING(@json, 2, LEN(@json)-2)));

    SELECT @output = '';

    WHILE (@json <> '') 
    BEGIN;

        IF (LEFT(@json, 1)<>'"')
            RETURN 'Expected quote (start of key name). Found "' + LEFT(@json, 1)+'"';

        SET @key=SUBSTRING(@json, 2, PATINDEX('%[^\\]"%', SUBSTRING(@json, 2, LEN(@json))+' "'))
        SET @json=LTRIM(SUBSTRING(@json, LEN(@key)+3, LEN(@json)));

        IF (LEFT(@json, 1)<>':')
            RETURN 'Expected ":" after key name, found "' + LEFT(@json, 1)+'"!';

        SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));

        IF (LEFT(@json, 1)='[')
            SELECT @array=1, @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));

        IF (@array IS NULL) 
            SET @array=0;

        WHILE (@array IS NOT NULL) 
        BEGIN

            SELECT @value=NULL, @nested=0;
            
            IF (@value IS NULL AND LEFT(@json, 1)='{') 
            BEGIN;

                SELECT @recursion_counter=1, @offset=1;

                WHILE (@recursion_counter<>0 AND @offset<LEN(@json)) 
                BEGIN
                    SET @offset=@offset+ PATINDEX('%[{}]%', SUBSTRING(@json, @offset+1, LEN(@json)));
                    SET @recursion_counter=@recursion_counter + (CASE SUBSTRING(@json, @offset, 1) WHEN '{' THEN 1 WHEN '}' THEN -1 END);
                END

                SET @value=CAST( dbo.fncJson2xml(LEFT(@json, @offset)) AS varchar(max));
                SET @json=SUBSTRING(@json, @offset+1, LEN(@json));
                SET @nested=1;

            END

            IF (@value IS NULL AND LEFT(@json, 2)='""')
                SELECT @value='', @json=LTRIM(SUBSTRING(@json, 3, LEN(@json)));

            IF (@value IS NULL AND LEFT(@json, 1)='"') 
            BEGIN
                SET @value=SUBSTRING(@json, 2, PATINDEX('%[^\\]"%', SUBSTRING(@json, 2, LEN(@json))+' "'));
                SET @json=LTRIM(SUBSTRING(@json, LEN(@value)+3, LEN(@json)));
            END

            IF (@value IS NULL AND LEFT(@json, 1)=',')
                SET @value='';

            IF (@value IS NULL) 
            BEGIN
                SET @value=LEFT(@json, PATINDEX('%[,}]%', REPLACE(@json, ']', '}')+'}')-1);
                SET @json=SUBSTRING(@json, LEN(@value)+1, LEN(@json));
            END;

            SET @output = @output + @lf + @cr + REPLICATE(@tab, @@NESTLEVEL-1)+ '<' + REPLACE(@key, '@', '') + '>'+ ISNULL(REPLACE( REPLACE(@value, '\"', '"'), '\\', '\'), '')+ (CASE WHEN @nested=1 THEN @lf+@cr+REPLICATE(@tab, @@NESTLEVEL-1) ELSE '' END) + '</' + REPLACE(@key, '@', '') + '>'

            IF (@array=0 AND @json <> '' AND LEFT(@json, 1) <> ',')
                RETURN @output+'Expected "," after value, found "'+ LEFT(@json, 1)+'"!';

            IF (@array=1 AND LEFT(@json, 1) NOT IN (',', ']'))
                RETURN @output+'In array, expected "]" or "," after '+ 'value, found "'+LEFT(@json, 1)+'"!';

            IF (@array=1 AND LEFT(@json, 1)=']') 
            BEGIN
                SET @array=NULL;
                SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));

                IF (LEFT(@json, 1) NOT IN ('', ',')) 
                BEGIN
                    RETURN 'Closed array, expected ","!';
                END
            END

            
            SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)+1));
            IF (@array=0) SET @array=NULL;

        END;
    END;

    RETURN CAST(@output AS XML);

END;
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

CREATE FUNCTION dbo.fncJson2xml (

@json VARCHAR(MAX)

)

RETURNS XML

BEGIN

DECLARE

@output VARCHAR(MAX),

@key VARCHAR(MAX),

@value VARCHAR(MAX),

@recursion_counter INT,

@offset INT,

@nested BIT,

@array BIT,

@tab CHAR(1) = CHAR(9),

@cr CHAR(1) = CHAR(13),

@lf CHAR(1) = CHAR(10);

SET @json=LTRIM(RTRIM( REPLACE(REPLACE(REPLACE(@json, @cr, ''), @lf, ''), @tab, '')));

IF (LEFT(@json, 1)<>'{' OR RIGHT(@json, 1)<>'}')

RETURN '';

SET @json=LTRIM(RTRIM(SUBSTRING(@json, 2, LEN(@json)-2)));

SELECT @output = '';

WHILE (@json <> '')

BEGIN;

IF (LEFT(@json, 1)<>'"')

RETURN 'Expected quote (start of key name). Found "' + LEFT(@json, 1)+'"';

SET @key=SUBSTRING(@json, 2, PATINDEX('%[^\\]"%', SUBSTRING(@json, 2, LEN(@json))+' "'))

SET @json=LTRIM(SUBSTRING(@json, LEN(@key)+3, LEN(@json)));

IF (LEFT(@json, 1)<>':')

RETURN 'Expected ":" after key name, found "' + LEFT(@json, 1)+'"!';

SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));

IF (LEFT(@json, 1)='[')

SELECT @array=1, @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));

IF (@array IS NULL)

SET @array=0;

WHILE (@array IS NOT NULL)

BEGIN

SELECT @value=NULL, @nested=0;

IF (@value IS NULL AND LEFT(@json, 1)='{')

BEGIN;

SELECT @recursion_counter=1, @offset=1;

WHILE (@recursion_counter<>0 AND @offset<LEN(@json))

BEGIN

SET @offset=@offset+ PATINDEX('%[{}]%', SUBSTRING(@json, @offset+1, LEN(@json)));

SET @recursion_counter=@recursion_counter + (CASE SUBSTRING(@json, @offset, 1) WHEN '{' THEN 1 WHEN '}' THEN -1 END);

END

SET @value=CAST( dbo.fncJson2xml(LEFT(@json, @offset)) AS varchar(max));

SET @json=SUBSTRING(@json, @offset+1, LEN(@json));

SET @nested=1;

END

IF (@value IS NULL AND LEFT(@json, 2)='""')

SELECT @value='', @json=LTRIM(SUBSTRING(@json, 3, LEN(@json)));

IF (@value IS NULL AND LEFT(@json, 1)='"')

BEGIN

SET @value=SUBSTRING(@json, 2, PATINDEX('%[^\\]"%', SUBSTRING(@json, 2, LEN(@json))+' "'));

SET @json=LTRIM(SUBSTRING(@json, LEN(@value)+3, LEN(@json)));

END

IF (@value IS NULL AND LEFT(@json, 1)=',')

SET @value='';

IF (@value IS NULL)

BEGIN

SET @value=LEFT(@json, PATINDEX('%[,}]%', REPLACE(@json, ']', '}')+'}')-1);

SET @json=SUBSTRING(@json, LEN(@value)+1, LEN(@json));

END;

SET @output = @output + @lf + @cr + REPLICATE(@tab, @@NESTLEVEL-1)+ '<' + REPLACE(@key, '@', '') + '>'+ ISNULL(REPLACE( REPLACE(@value, '\"', '"'), '\\', '\'), '')+ (CASE WHEN @nested=1 THEN @lf+@cr+REPLICATE(@tab, @@NESTLEVEL-1) ELSE '' END) + '</' + REPLACE(@key, '@', '') + '>'

IF (@array=0 AND @json <> '' AND LEFT(@json, 1) <> ',')

RETURN @output+'Expected "," after value, found "'+ LEFT(@json, 1)+'"!';

IF (@array=1 AND LEFT(@json, 1) NOT IN (',', ']'))

RETURN @output+'In array, expected "]" or "," after '+ 'value, found "'+LEFT(@json, 1)+'"!';

IF (@array=1 AND LEFT(@json, 1)=']')

BEGIN

SET @array=NULL;

SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));

IF (LEFT(@json, 1) NOT IN ('', ','))

BEGIN

RETURN 'Closed array, expected ","!';

END

SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)+1));

IF (@array=0) SET @array=NULL;

END;

RETURN CAST(@output AS XML);

END;

É isso aí.
Até o próximo post!

Marcos Cezar Ruiz da Silva disse:

14 de agosto de 2020 às 17:46

ta bloqueado para copiar o codigo ?

Responder
- Dirceu Resende disse:
  
  15 de agosto de 2020 às 17:02
  
  Não deveria.. Vou dar uma olhada
  
  Responder
tiagolneves disse:

25 de julho de 2015 às 20:37

Muito boa a dica.

Responder

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

Como utilizar JSON no SQL Server 2008, 2012 e 2014 – Lendo strings JSON, importando para o banco e exportando para XML

O que é JSON

Lendo uma string JSON

Criando uma string JSON a partir do banco

Criando um XML a partir de um JSON

Solução alternativa para converter uma string JSON para XML

You may also like...

3 Responses

Deixe uma respostaCancelar resposta

O que é JSON

Lendo uma string JSON

Criando uma string JSON a partir do banco

Criando um XML a partir de um JSON

Solução alternativa para converter uma string JSON para XML

You may also like...

SQL Server e Power BI: Como carregar dados de Stored Procedure no SQL Server com DirectQuery

SQL Server – Como identificar timeout ou conexões interrompidas utilizando Extended Events (XE) ou SQL Profiler (Trace)

SQL Server – The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE

3 Responses

Deixe uma respostaCancelar resposta