{"id":4410,"date":"2018-07-10T21:59:40","date_gmt":"2018-07-10T21:59:40","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=4410"},"modified":"2023-06-14T15:26:48","modified_gmt":"2023-06-14T15:26:48","slug":"qlik-rest-connector-examples-read-json-xml-api","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/qlik-rest-connector-examples-read-json-xml-api\/","title":{"rendered":"Qlik REST Connector Examples – Read JSON \/ XML API"},"content":{"rendered":"

Introduction<\/h2>\n

\"QlikSense<\/a>In this article, we will learn to create a Qlik REST connector to OneDrive, Google, and other services.<\/p>\n

Qlik is a very powerful tool to analyze data from multiple sources and\u00a0visualize, create relations and charts. It is a simple, but powerful tool used\u00a0to visualize data.<\/p>\n

In this tutorial, we will show how to get data from the REST API in Qlik. We will\u00a0use the ZappySys ODBC PowerPack<\/a> which is a very useful tool used to get data\u00a0from REST API, WEB API, JSON files, XML files, SOAP and OData using simple SQL queries to the data.
\nThis article will show how to extract data from Facebook, Twitter, and Google\u00a0Sheets in Qlik using REST API.<\/p>\n

Requirements for Qlik REST connector<\/h2>\n
    \n
  1. First of all, you will need Qlik Sense for Desktop.<\/li>\n
  2. Secondly, ZappySys ODBC PowerPack<\/a> installed.<\/li>\n<\/ol>\n

    Getting started – Reading data form API<\/h2>\n

    We will first show how to connect to OData in JSON format.<\/p>\n

    Configure ODBC Data Source for API Connection<\/h3>\n
      \n
    1. First of all, we will access the following URL:\n
      https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Orders?$format=json<\/pre>\n<\/li>\n
    2. Secondly, in the windows start menu, Search for \u201cODBC\u201d open the ODBC Data Sources.<\/li>\n
    3. Also, in the ODBC Administrator, press Add and select the ZappySys JSON
      \nDriver:<\/p>\n
    4. Finally, specify the URL of step 1 and save the configuration:\n
      \"REST<\/a>
      URL ODBC<\/p><\/div><\/li>\n<\/ol>\n
      Reading REST API data in Qlik (via ODBC Connector)<\/h3>\n
        \n
      1. First of all, open the Qlik Sense Desktop:\n
      2. Secondly, enter your login and password:\n
      3. Also, create a new App:\n
      4. Additionally, enter your App name:\n
      5. In addition, select the option Add data from files and other sources:\n
      6. Select the ODBC data source:\n
      7. Also, select the ZappySys DSN created in the Create Qlik REST connector to OData:\n
      8. Finally, you will be able to see the data and create graphs and more.\u00a0For more information about Qlik Sense refer to\u00a0this link<\/a>.\n
        \"Visualize<\/a>
        Qlik to Odata<\/p><\/div><\/li>\n<\/ol>\n
        Other REST API Examples in Qlik<\/h2>\n
        Now lets look at a\u00a0few more examples of other popular REST API services (JSON or XML API).<\/p>\n
        Google SpreadSheet \/ Drive API Example in Qlik<\/h3>\n
        In this new example, we will connect to a Google Sheet. The Sheet is the
        \nfollowing:<\/p>\n
        \"<\/a>
        Qlik to Google Sheet<\/p><\/div>\n
          \n
        1. First of all, create a new ZappySys JSON Driver Connection in the ODBC.<\/li>\n
        2. Secondly, you will need to specify the following link:\n
          https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/1yfNChaljZvAL-HXF4lmM4o16Paa_BZuBWSflgBssZAQ\/values\/A1:B4<\/pre>\n
          where 1yfNChaljZvAL-HXF4lmM4o16Paa_BZuBWSflgBssZAQ is the ID of the Google\u00a0Sheet and A1:B4 are the ranges of cells to retrieve:<\/p>\n
          \"File<\/a>
          Google file id<\/p><\/div><\/li>\n
        3. Also, in filter enter $.values[*] and select the OAuth Connection Type\u00a0and press the link to configure the OAuth connection:\n
        4. In addition, select Google OAuth Provider and specify the following\u00a0scope and Generate a Token:\n
        5. Finally, create an Application in Qlik Sense Desktop and add the ODBC\u00a0DSN created in the previous step and you will have the Google Sheet data:\n
          \"Google<\/a>
          Display data REST<\/p><\/div><\/li>\n<\/ol>\n
          Twitter REST API Example in Qlik<\/h3>\n
          In this new section, we will show how to connect to Twitter and get the
          \ninformation in Qlik using REST.<\/p>\n
            \n
          1. First of all, we will use the ODBC Administrator to use the ZappySys\u00a0ODBC PowerPack and select the ZappySys JSON Driver.<\/li>\n
          2. Secondly, we will enter the following URL in the ZS JSON Drive:\n
            https:\/\/api.twitter.com\/1.1\/statuses\/user_timeline.json?<\/pre>\n<\/li>\n
          3. Also in connection type, select OAuth:\n
          4. In Addition, in the OAuth parameters, select the Twitter OAuth provider\u00a0and generate a Token. The application will ask for your Twitter credentials:\n
          5. Finally, create a new app in Qlik Sense Desktop and add the ODBC DSN\u00a0just created and you will have your Twitter data in Qlik Sense.\n
            \"Connect<\/a>
            Qlik sense to Twitter<\/p><\/div><\/li>\n<\/ol>\n
            Facebook REST API Example in Qlik<\/h3>\n
            Facebook is another popular social network with interesting information. The\u00a0following example will show how to show the number of Facebook friends in Qlik\u00a0using REST API.<\/p>\n
              \n
            1. \u00a0First of all, open the ODBC Administrator in Windows and add the\u00a0ZappySys JSON Driver.<\/li>\n
            2. Secondly, you will need a Facebook application. The following link<\/a> shows\u00a0you how to configure the application.\n
            3. In your ZappySys ODBC driver, add your client id and client secret that can be obtained from the Facebook Application:\n
            4. Finally add your ODBC connection to Facebook in Qlik Sense as we did in previous examples.<\/li>\n<\/ol>\n
              ZappySys JSON \/REST API Driver Query Examples<\/h2>\n
              Reading from XML files or API can be done using the same way as previous sections except you have to use ZappySys XML Driver. Read help file here to see json query examples<\/a>.<\/p>\n
              ZappySys\u00a0XML \/ SOAP Driver Query Examples<\/h2>\n
              Reading from XML files or API can be done using the same way as previous sections except you have to use ZappySys XML Driver. Read help file here to see xml query examples<\/a>.<\/p>\n
              Calling XML SOAP Web Service in Qlik<\/h2>\n
              So far we have looked at examples to consume data using JSON driver. Now lets look at an example, to call XML SOAP Web Service in Qlik.<\/p>\n
              To call SOAP API you need to know Request XML Body Structure.\u00a0If you are not sure how to create SOAP Request body then no worries. Check this article<\/a> to learn how to generate SOAP Request body using the Free tool SoapUI<\/a>. Basically, you have to use SoapUI to generate Request XML and after that, you can replace parameters as needed in the generated body.\r\n
              What is SOAP Web Service?<\/h3>\r\nIf you are new to SOAP Web Service sometimes referred as XML Web Service then please read some concept\u00a0about\u00a0SOAP\u00a0Web service standard from this link<\/a>\r\n\r\nThere are two important aspects in SOAP Web service.\r\n
                \r\n \t
              1. Getting WSDL file or URL<\/li>\r\n \t
              2. Knowing exact Web Service URL<\/li>\r\n<\/ol>\r\n
                What is WSDL<\/h3>\r\nIn very simple term WSDL (often pronounced as whiz-dull) is nothing but a document which describes Service metadata (e.g. Functions you can call, Request parameters, response structure etc). Some service simply give you WSDL as xml file you can download on local machine and then\u00a0analyze or sometimes you may get direct URL (e.g. http:\/\/api.mycompany.com\/hr-soap-service\/?wsdl )\r\n
                Example SQL Query for SOAP API call using ZappySys XML Driver<\/h3>\r\nHere is an example SQL query you can write to call SOAP API. If you not sure about many details then check next few sections on how to use XML Driver User Interface to build desired SQL query to POST data to XML SOAP Web Service without any coding.\r\n
                SELECT * FROM $\r\nWITH(\r\n\t Src='http:\/\/www.holidaywebservice.com\/HolidayService_v2\/HolidayService2.asmx'\r\n\t,DataConnectionType='HTTP'\r\n\t,CredentialType='Basic' --OR SoapWss\r\n\t,SoapWssPasswordType='PasswordText'\r\n\t,UserName='myuser'\r\n\t,Password='pass$$w123'\r\n\t,Filter='$.soap:Envelope.soap:Body.GetHolidaysAvailableResponse.GetHolidaysAvailableResult.HolidayCode[*]'\r\n\t,ElementsToTreatAsArray='HolidayCode'\t\r\n\t,RequestMethod='POST'\t\r\n\t,Header='Content-Type: text\/xml;charset=UTF-8 || SOAPAction: \"http:\/\/www.holidaywebservice.com\/HolidayService_v2\/GetHolidaysAvailable\"'\r\n\t,RequestData='\r\n<soapenv:Envelope xmlns:soapenv=\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\" xmlns:hol=\"http:\/\/www.holidaywebservice.com\/HolidayService_v2\/\">\r\n   <soapenv:Header\/>\r\n   <soapenv:Body>\r\n      <hol:GetHolidaysAvailable>\r\n         <!--type: Country - enumeration: [Canada,GreatBritain,IrelandNorthern,IrelandRepublicOf,Scotland,UnitedStates]-->\r\n         <hol:countryCode>UnitedStates<\/hol:countryCode>\r\n      <\/hol:GetHolidaysAvailable>\r\n   <\/soapenv:Body>\r\n<\/soapenv:Envelope>'\r\n)<\/pre>\r\nNow let's look at steps to create SQL query to call SOAP API. Later we will see how to generate code for your desired programming language (e.g. C# or SQL Server)\r\n
                Video Tutorial - Introduction to SOAP Web Service and SoapUI tool<\/h3>\r\nBefore we dive into details about calling SOAP API using ZappySys XML Driver, lets first understand what is SOAP API and how to create SOAP requests using SoapUI tool. You will learn more about this process in the\u00a0later section. The video contains some fragment about using SOAP API in SSIS but just ignore that part because we will be calling Soap API using ZappySys ODBC Driver rather than SSIS Components.\r\n\r\n \r\n\r\n