REST Connector support for nested JSON and XML response

p_verkooijen — Wed, 06 Jul 2022 10:59:11 GMT

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-JSON/tac-p/1951837#M6804

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-such-that-I-get-a-flat/td-p/1568751

Re: REST Connector support for nested JSON and XML response

stevedark — Tue, 12 Jul 2022 13:52:24 GMT

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

Re: REST Connector support for nested JSON and XML response

p_verkooijen — Thu, 14 Jul 2022 10:23:05 GMT

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>

Re: REST Connector support for nested JSON and XML response

stevedark — Thu, 14 Jul 2022 11:30:46 GMT

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

Re: REST Connector support for nested JSON and XML response

p_verkooijen — Fri, 29 Jul 2022 06:30:45 GMT

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.

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

Meghann_MacDonald — Wed, 02 Aug 2023 16:21:16 GMT

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.

Re: REST Connector support for nested JSON and XML response - Status changed to: Closed - Archived

Ideation — Wed, 02 Aug 2023 16:21:18 GMT

idea REST Connector support for nested JSON and XML response in Suggest an Idea

REST Connector support for nested JSON and XML response

Re: REST Connector support for nested JSON and XML response

Re: REST Connector support for nested JSON and XML response

Re: REST Connector support for nested JSON and XML response

Re: REST Connector support for nested JSON and XML response

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

Re: REST Connector support for nested JSON and XML response - Status changed to: Closed - Archived