Suggest an Idea

p_verkooijen · ‎2022-07-05

Created from a old Knowledge base article from @Sonja_Bauernfeind

https://community.qlik.com/t5/Knowledge/REST-connector-loads-a-large-number-of-table-from-a-nested-J...

When using Qlik REST connector to load data from API sources that return JSON or XML response, sometimes a huge data model with many tables is returned although the response contains only a single table.

For example, the following message is parsed into 5 single-row tables instead of one table with 5 rows:

When saved to disk an loaded as a table Qlik does turns the data in a logic model

The REST Connector should support this method.

Also see discussion https://community.qlik.com/t5/Qlik-Sense-Data-Connectivity/Can-I-query-nested-XML-data-over-REST-suc...

stevedark · ‎2022-07-12

Totally agree that this should be supported. That said though, it is rare that I pull a set of data from a REST endpoint that doesn't need some hacking about with the RESIDENT loads to make it better - for instance turning two column dimension tables into ApplyMap statements.

Steve

p_verkooijen · ‎2022-07-14

Hi @stevedark, I'm not a stranger to creative solutions.

However everyone's life could be so much easier when the REST connector would support the same as dealing with the table file itself. So having to workaround it when you shouldn't have to feels quite silly.

The example was based on an old Knowledgebase article

I was dealing with this nested array. Tried to find a way to put the response in 1 field and use the From_Field from there, this would create a way to parse the response/field like a file. No success so far with this.

Using the XML file, life is so easy 😎

Using REST Connector 😒

File content

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/">
   <SOAP-ENV:Body SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:NS1="urn:TPAPIPosIntfU-ITPAPIPOS">
      <NS1:GetGroupsInfoResponse xmlns:NS2="urn:TPAPIPosIntfU" xmlns:NS3="urn:TPAPIPosTypesU">
         <return xsi:type="NS2:TGetGroupsInfoResponse">
            <ReturnCode xsi:type="xsd:int">0</ReturnCode>
            <ReturnMessage xsi:type="xsd:string">ok</ReturnMessage>
            <Groups xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TGroupInfo[8]">
               <item xsi:type="NS3:TGroupInfo">
                  <GroupId xsi:type="xsd:long">5000000006</GroupId>
                  <GroupName xsi:type="xsd:string">Drinks</GroupName>
                  <CategoryId xsi:type="xsd:long">5000000003</CategoryId>
                  <HqId xsi:type="xsd:string">Drinks</HqId>
                  <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[2]">
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_turnover</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_vat</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                  </Extra>
               </item>
               <item xsi:type="NS3:TGroupInfo">
                  <GroupId xsi:type="xsd:long">5000000007</GroupId>
                  <GroupName xsi:type="xsd:string">Food</GroupName>
                  <CategoryId xsi:type="xsd:long">5000000004</CategoryId>
                  <HqId xsi:type="xsd:string">Food</HqId>
                  <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[2]">
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_turnover</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_vat</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                  </Extra>
               </item>
               <item xsi:type="NS3:TGroupInfo">
                  <GroupId xsi:type="xsd:long">5000000008</GroupId>
                  <GroupName xsi:type="xsd:string">Instructions</GroupName>
                  <CategoryId xsi:type="xsd:long">5000000005</CategoryId>
                  <HqId xsi:type="xsd:string">Instructions</HqId>
                  <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[2]">
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_turnover</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_vat</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                  </Extra>
               </item>
               <item xsi:type="NS3:TGroupInfo">
                  <GroupId xsi:type="xsd:long">40000145435</GroupId>
                  <GroupName xsi:type="xsd:string">Deposits</GroupName>
                  <CategoryId xsi:type="xsd:long">40000145437</CategoryId>
                  <HqId xsi:type="xsd:string"/>
                  <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[2]">
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_turnover</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_vat</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                  </Extra>
               </item>
               <item xsi:type="NS3:TGroupInfo">
                  <GroupId xsi:type="xsd:long">65000329195</GroupId>
                  <GroupName xsi:type="xsd:string">Coctails</GroupName>
                  <CategoryId xsi:type="xsd:long">65000326742</CategoryId>
                  <HqId xsi:type="xsd:string">Coctails</HqId>
                  <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[2]">
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_turnover</Key>
                        <Value xsi:type="xsd:string">270000</Value>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_vat</Key>
                        <Value xsi:type="xsd:string">270000</Value>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                  </Extra>
               </item>
               <item xsi:type="NS3:TGroupInfo">
                  <GroupId xsi:type="xsd:long">65000337097</GroupId>
                  <GroupName xsi:type="xsd:string">Vouchers</GroupName>
                  <CategoryId xsi:type="xsd:long">65000326742</CategoryId>
                  <HqId xsi:type="xsd:string"/>
                  <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[2]">
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_turnover</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_vat</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                  </Extra>
               </item>
               <item xsi:type="NS3:TGroupInfo">
                  <GroupId xsi:type="xsd:long">65000355345</GroupId>
                  <GroupName xsi:type="xsd:string">Savepoints</GroupName>
                  <CategoryId xsi:type="xsd:long">65000326742</CategoryId>
                  <HqId xsi:type="xsd:string">Savepoints</HqId>
                  <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[2]">
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_turnover</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_vat</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                  </Extra>
               </item>
               <item xsi:type="NS3:TGroupInfo">
                  <GroupId xsi:type="xsd:long">65000498501</GroupId>
                  <GroupName xsi:type="xsd:string">Miscellaneous</GroupName>
                  <CategoryId xsi:type="xsd:long">65000498502</CategoryId>
                  <HqId xsi:type="xsd:string">Miscellaneous</HqId>
                  <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[2]">
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_turnover</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                     <item xsi:type="NS3:TExtraInfo">
                        <Key xsi:type="xsd:string">bookkeeping_vat</Key>
                        <Value xsi:type="xsd:string"/>
                        <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
                     </item>
                  </Extra>
               </item>
            </Groups>
            <Extra xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="NS3:TExtraInfo[0]"/>
         </return>
      </NS1:GetGroupsInfoResponse>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

stevedark · ‎2022-07-14

Totally agree that a workaround shouldn't be neccesary in this case, but I'm going to post another workaround anyway. 😁

With Qlik Automation you could probably set up a routine to pull the XML and drop it into a file repository that is linked to SaaS and then load from there as a flat file - which would give you the three tables correctly.

Would be great to see it implemented natively though, without the need for any extra step.

Steve

p_verkooijen · ‎2022-07-29

Thnx for the idea, indeed Qlik Automation could be a additional option .

This data example limits to a quite simple table, the real deal is an transactional dataset.
Not sure how I would get a incremental load setup based on last transaction datetime or id received using Qlik Automation.

Meghann_MacDonald · ‎2023-08-02

From now on, please track this idea from the Ideation portal.

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.

Ideation · ‎2023-08-02

Suggest an Idea

REST Connector support for nested JSON and XML response

REST Connector support for nested JSON and XML response