Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Suggest an Idea

Announcements
This page is no longer in use. To suggest an idea, please visit Browse and Suggest.

REST Connector support for nested JSON and XML response

p_verkooijen
Partner - Specialist II
Partner - Specialist II
‎2022-07-05 10:00 AM

REST Connector support for nested JSON and XML response

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:

p_verkooijen_0-1657029128115.png

 

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

p_verkooijen_1-1657029163009.png

 

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...

 

Status: Closed - Archived Submitted by Partner - Specialist II on ‎2022-07-05 10:00 AM
1,967 Views
6 Comments
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP
‎2022-07-12 09:52 AM

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
Partner - Specialist II
Partner - Specialist II
‎2022-07-14 06:23 AM

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 😎

  • p_verkooijen_0-1657793344844.png

Using REST Connector 😒

  • p_verkooijen_1-1657793411502.png

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
Partner Ambassador/MVP
Partner Ambassador/MVP
‎2022-07-14 07:30 AM

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
Partner - Specialist II
Partner - Specialist II
‎2022-07-29 02:30 AM

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
Employee
Employee
‎2023-08-02 12:21 PM

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
Newbie
Newbie
‎2023-08-02 12:21 PM
 
Status changed to: Closed - Archived
Subscribe by Topic