How to exact 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)