Skip to main content

Qlik Application Automation

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Koen_D
Contributor III
Contributor III

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

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=InvoiceNum.... 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=InvoiceNum... 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>
<updated>2022-11-11T10:45:35Z</updated>
<link rel="self" title="ReceivablesList" href="ReceivablesList" />
<entry>
<title type="text"></title>
<updated>2022-11-11T10:45:35Z</updated>
<author>
<name />
</author>
<link rel="edit" title="Receivable" href="ReceivablesList(1L)" />
<category term="Exact.Web.Api.Models.Receivable" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
<content type="application/xml">
<m:properties>
<d:InvoiceNumber m:type="Edm.Int32">1</d:InvoiceNumber>
</m:properties>
</content>
</entry>
<entry>
</feed>
Labels (2)
1 Solution

Accepted Solutions
J_Lindberg
Support
Support

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)?

View solution in original post

3 Replies
Koen_D
Contributor III
Contributor III
Author

Hello Qlik Community,

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

 

{
"d": {
"results": [
{
"__metadata": {
"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:
 
MasterTable:
SQL SELECT "d" as "d"
FROM JSON (wrap on) "root"
WITH CONNECTION (
URL "$(vURL)",
HTTPHEADER "Authorization" "Bearer $(vAccessToken)",
HTTPHEADER "Accept" "$(vAccept)",
BODY "$(vRequestBody)"
);
 
Any ideas?
J_Lindberg
Support
Support

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)?

Koen_D
Contributor III
Contributor III
Author

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