Retrieving data from data source that provides data in XML/Json format

Koen_D — Fri, 11 Nov 2022 12:00:54 GMT

Hello Qlik experts,

I've been struggling to get Qlik Sense to communicate with a database (Exact Online) that uses OAuth2. I've managed to get the token aspect to work, however now I'm stuck on how to retrieve the data from the database.

What I've done:

1. Set up an authorization process and a POST call in Qlik to get my access and refresh tokens.

2. Automatically get new access token if needed.

I've also managed to connect to the database using the access token. I know this, because my script gives an error message indicating my access token is no longer valid if I try to access it without the proper token, while giving a different parsing error when I use the correct token.

The endpoint I'm trying to contact is as shown here: https://start.exactonline.nl/docs/HlpRestAPIResources.aspx?SourceAction=10 and has the form of (for example): https://start.exactonline.nl/api/v1/{DivisionCode}/read/financial/ReceivablesList?$select=InvoiceNumber. This would fetch the InvoiceNumbers as simple example.

I've managed to get this to work in Postman. The result I get I've added to the bottom of this post. The setting I use in postman are GET https://start.exactonline.nl/api/v1/{DivisionCode}/read/financial/ReceivablesList?$select=InvoiceNumber, and Authorization Type Bearer Token.

In Qlik, I have the following code:

LIB CONNECT TO 'REST_GET'; // dummy connector

set vURL = 'https://start.exactonline.nl/api/v1/3020858/read/financial/ReceivablesList?$select=InvoiceNumber';

set vRequestBody = '';

MasterTable:
SQL SELECT "InvoiceNumber"
FROM JSON (wrap on) "root"
WITH CONNECTION (
URL "$(vURL)",
HTTPHEADER "Authorization" "Bearer $(vAccessToken)",
BODY "$(vRequestBody)"
);

Which gives the following error:

(Connector error: Unexpected character encountered while parsing value: <. Path '', line 0, position 0.)

I think I'm messing up in the select statement, as the connector seems to work. I've tried a lot there, like using specific XML statements. I'm not sure though. I get the same error no matter what input I give in the select statement.

Any tips?

Postman results (shortened and anonymised):

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

<title type="text">ReceivablesList</title>

<id>https://start.exactonline.nl/api/v1/{DivisionCode}/read/financial/ReceivablesList</id>

<entry>

<id>https://start.exactonline.nl/api/v1/{DivisionCode}/read/financial/ReceivablesList(1L)</id>

</author>

<m:properties>

<d:InvoiceNumber m:type="Edm.Int32">1</d:InvoiceNumber>

</m:properties>

</content>

</entry>

<entry>

</feed>

Re: Retrieving data from data source that provides data in XML/Json format

Koen_D — Fri, 11 Nov 2022 13:41:43 GMT

Hello Qlik Community,

I've managed to get postman to give me the following result after a GET request:

{

"d": {

"results": [

{

"__metadata": {

"uri": "https://start.exactonline.nl/api/v1/{Code}/financial/GLAccounts(guid'1')",

"type": "Exact.Web.Api.Models.GLAccount"

"ID": "1,

"AssimilatedVATBox": null }

]

}

I've managed to connect to the database from Qlik, but not managed to retrieve data yet. It now loads 0 lines of data. I've tried the following statement:

MasterTable:
SQL SELECT "d" as "d"
FROM JSON (wrap on) "root"
WITH CONNECTION (
URL "$(vURL)",
HTTPHEADER "Authorization" "Bearer $(vAccessToken)",
HTTPHEADER "Accept" "$(vAccept)",
BODY "$(vRequestBody)"
);

I've also tried:

Any ideas?

Re: Retrieving data from data source that provides data in XML/Json format

J_Lindberg — Fri, 11 Nov 2022 13:59:24 GMT

hello @Koen_D,

In this forum we would be able to help you with questions related to the product Qlik Application Automation.
This question seems to be related to Qlik Sense, if so, could you please post your question in that forum (https://community.qlik.com/t5/Qlik-Sense/ct-p/qlik-sense)?

Re: Retrieving data from data source that provides data in XML/Json format

Koen_D — Fri, 11 Nov 2022 14:51:23 GMT

Ah, I'm sorry, I'll delete and repost.

topic Retrieving data from data source that provides data in XML/Json format in Qlik Automate

Retrieving data from data source that provides data in XML/Json format

Re: Retrieving data from data source that provides data in XML/Json format

Re: Retrieving data from data source that provides data in XML/Json format

Re: Retrieving data from data source that provides data in XML/Json format