Loading XML files from web via REST connector

    Since Qlik Sense introduced data connections it was imposible to load multiple tables from web source in a automated way and you needed a LIB data connection for each and every source table. Recently I was asked to migrate from QlikView to Qlik Sense an app which was processing tens of xml files containing exchange rates. Every month there was a new xml file and I wanted to avoid creating a data connection each month, so I was trying this and that. The source was a xml file and I remembered that REST Connector is able to process xml data, so I wondered: what if I use URL of that file to create a REST data connection and then use WITH CONNECTION parameter to change the source URL dynamically?

     

    And, it did work! So here's how to do it.

     

    First, you need to create a REST data connection to an URL. In my case it was

    http://www.nbs.sk/_img/Documents/_KurzovyListok/KLVCM/EX201710.XML
    
    
    

    This file resides on the website of the Slovak Central Bank and it contains exchange rates of so called "exotic currencies". Look at the file name. It consists of year and month for which the file is valid. And I needed to load all files starting from January 2009. But back to the data connection. Basically, the only thing I needed to set here (apart from URL) was Authentication and I set it to Anonymous, since we're not really authenticating anywhere.

     

    After creating a connection I created a request (in fact two requests, see attached app) to load data. Next step was to create a loop which will go through all months starting from January 2009 until today and create a correct URL, which will be later used in the WITH CONNECTION parameter of the load statement.

    mmax = month(today(0));
    ymax = year(today(0));
    
    
    for vYear =2009 to ymax
        for vMonth = 1 to 12
            if (vYear * 12 + vMonth) <= (ymax * 12 + mmax) then
                vDateNo = vYear * 100 + vMonth
                vFile= 'http://www.nbs.sk/_img/Documents/_KurzovyListok/KLVCM/EX' & vDateNo & '_en.XML';
    //LOAD statement goes here
    
              EndIf
    
         Next vMonth
    
    Next vYear       
    
    
    

     

    The last step was to use the WITH CONNECTION parameter for the REST call. Basically, WITH CONNECTION allows you to customize your REST calls, adding query parameters, and changing parameters, including the target URL (see https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/1.2/Load-REST-data/Select-statement-syntax-REST.htm for details). So within the loop I used two REST calls, one of them follows:

    SQL SELECT
        "validFrom",
        "number",
        "__KEY_nbsExtRateList",
        (SELECT
        FROM "rateList")
    FROM XML "nbsExtRateList" PK "__KEY_nbsExtRateList" QDL
    WITH CONNECTION (Url "$(vFile)");
    
    
    

     

    Almost done, I just needed to join tables resulting from the two REST calls. See attached app for full script.

     

    Note 1: Since REST connector can process xml, csv and json I suppose it is possible to only load these sources in this manner. So no html tables as it was possible in QlikView.

     

    Note 2: This will be soon invalidated by the February 2018 release of Qlik Sense, which will introduce dynamic URLs for web files, so it will be much easier to load data from web in loops. Until then, enjoy this workaround!

     

    EDIT: Adding Note 3 based on comment by Steve Dark (stevedark)

    Note 3: It is possible to enable the functionality where you can do everything you can in a QlikView load script. In Desktop this is modifying a config file, in Enterprise it is in QMC, untick Standard Mode in the Engine settings (Editing an engine ‒ Qlik Sense). You need to be very aware of the security implications of disabling this property though, as it will mean that anyone who can edit script can access anywhere on the servers file system that the service account has access to (as access is delegated through the service).