2 Replies Latest reply: Feb 17, 2016 3:29 AM by Petter Skjolden RSS

    Qlikview REST connector pagination XML

    Peter Cappelle

      Hi,

       

      We have a XML file that is on somewebsite and looks in a way like this (confidential parts stripped:

       

      <?xml version="1.0" encoding="utf-8" standalone="yes"?>
      <feed xml:base="https://somewebsite.com/crm/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
        <title type="text">Accounts</title>
        <id></id>
        <updated>2016-02-04T08:36:56Z</updated>
        <link rel="self" title="Accounts" href="Accounts" />
      <entry>
          <title type="text"></title>
          <updated>2016-02-04T08:36:56Z</updated>
          <author>
            <name />
          </author>
          <content type="application/xml">
            <m:properties>
              <d:Type>A</d:Type>
              <d:UniqueTaxpayerReference m:null="true" />
              <d:VATLiability m:null="true" />
              <d:VATNumber m:null="true" />
              <d:Website m:null="true" />
            </m:properties>
          </content>
        </entry>
        <link rel="next" href="https://somewebsite.com/Accounts?$skiptoken=guid'ee6bc390-a8ac-4bbd-8a4d-0a1f04ab9bd3'" />
      </feed>
      
      

       

      We use the new Rest connector to get the data out of this XML file.

      The XML has pagination and every 60 entries you can load the next 60 with the link at the bottom of this xml file.

       

      The problem i have is when, in the REST connector, we want to enable pagination with these setting:

       

      Pagination Type: Next URL

      Next URL field path: /*[name()="feed"]/*[name()="link"][contains(@rel,"next")]/@href

       

      It doesn't seem to work...

       

      side note: the XML file has namespaces so i need to target the elements this way instead of /feed/link/...

       

      I'm using Xpath syntax to target the link href, but maybe this is not the way to go? Or maybe the REST connector isn't using Xpath syntax?

       

      Really hope someone can help me!

       

      Regards Peter

        • Re: Qlikview REST connector pagination XML
          Peter Cappelle

          I can't imagine that nobody has an answer on my question ... ?

          • Re: Qlikview REST connector pagination XML
            Petter Skjolden

            Actually it turns out that this seems to be due to a "bug" in the Qlik REST Connector 1.0 so the pagination doesn't work.

             

            But there is a fix for it:

             


            1) Make sure that the Qlik REST Connector 1.0 connect dialog box has the NextUrl set to:

             

                      feed/link/attr:href

             


            2) When the SQL has been generated after using the SELECT-button and going through the wizard you have to modify the sub-SELECT that reads like this:

             

            .....
            (SELECT
            "attr:rel" AS "rel",
            "attr:title" AS "title",
            "attr:href" AS href,
            "__FK_link"
            FROM "link" FK "__FK_link"),
            .....
            
            
            

             

             

            On line 05 you will have to remove the text AS href.

             

            So it should look like this:

             

            .....
            (SELECT
            "attr:rel" AS "rel",
            "attr:title" AS "title",
            "attr:href",
            "__FK_link"
            FROM "link" FK "__FK_link"),
            ....
            
            

             


            3) Find the LOAD statement that loads from this sub-select with a RESIDENT [MasterREST further down in the load script and make sure that the reference to href is changed to [attr:href] - or else you will get an error message while loading.

            Should look like this after the change:

            [link]:
            LOAD [rel],
              [title],
              [attr:href],
              [__FK_link] AS [__KEY_feed]
            RESIDENT RestConnectorMasterTable
            WHERE NOT IsNull([__FK_link]);