Qlik Community

Qlik Sense Enterprise Documents

Documents for Qlik Sense related information.

How to extract JSON data from Qlik Sense Application using SQL Server 2016

wdchristensen
Valued Contributor

How to extract JSON data from Qlik Sense Application using SQL Server 2016

How to exact JSON data from Qlik Sense Application in SQL Server 2016

1. Download and install the Backup & Restore extension (GitHub – countnazgul)

https://github.com/countnazgul/QS-backup-and-restore-app/releases

2. Use the backup function to create a json file of the app.

3. Open SSMS and connect to SQL Server 2016 or above

4. Copy the TSQL below into SSMS and edit the @variables as needed to create the appropriate dynamic sql

5. Run and the results are in a user friendly format!

Example below extracts variable names and definition from a Qlik Sense application.

Declare @vKeyType as varchar(100) = 'variables'

Declare @vColumnNames as varchar(1000) =

'qInfo NVARCHAR(100), qId NVARCHAR(100), qType NVARCHAR(100), qMetaDef NVARCHAR(100),qName NVARCHAR(100), qComment NVARCHAR(100), qNumberPresentation NVARCHAR(100), qType NVARCHAR(100), qnDec NVARCHAR(100), qUseThou NVARCHAR(100), qDefinition NVARCHAR(100)'

/* Column names are specific to the KeyType. This example uses 'variables' and will need to change if KeyType changes. If you have questions you can email me at wdchristensen@gmail.com */

Declare @vFileName as varchar(1000) = '\\ServerName\c$\Users\SomeUserId\Downloads\AppName.json'

Declare @vSQL_01 as varchar(2000) = 'SELECT *

FROM OPENROWSET (BULK ' + ''''+ @vFileName + '''' + ', SINGLE_CLOB) as j

CROSS APPLY OPENJSON(BulkColumn, ''$.variables'')'

PRINT @vSQL_01

EXEC (@vSQL_01)

Declare @vSQL_02 as varchar(2000) = 'SELECT *

FROM OPENROWSET (BULK ' + '''' + @vFileName + '''' + ', SINGLE_CLOB) as j

CROSS APPLY OPENJSON(BulkColumn, ' + '''' + '$.' + @vKeyType + '''' + ')

WITH ( ' + @vColumnNames + ') '

PRINT  @vSQL_02

EXEC (@vSQL_02)

Attachments
Comments
ecolomer
Honored Contributor II

Thank's for sharing.

Saludos

Enrique Colomer

Version history
Revision #:
1 of 1
Last update:
‎05-19-2017 05:16 PM
Updated by: