Qlik Community

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
crossroadsit
Contributor II
Contributor II

Can I query nested XML data (over REST) such that I get a flat table?

Hey folks.

I am using Qlik to report on vehicle usage data. The data is in a tracking system that provides a rather limited SOAP API returning XML data that looks like...

 

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns="fleettestlive.cartrack.com.hk/api/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
   <SOAP-ENV:Body>
      <ns1:endpoint.get_all_tripsResponse xmlns:ns1="fleettestlive.cartrack.com.hk/api/">
         <SqlResult0 xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="tns:get_all_tripsResultType0Row[472]">
            <item xsi:type="tns:get_all_tripsResultType0Row">
               <vehicle_id xsi:type="xsd:decimal">1234</vehicle_id>
               <start_timestamp xsi:type="xsd:string">2017-03-19 16:51:09+08</start_timestamp>
               <end_timestamp xsi:type="xsd:string">2017-03-19 16:57:17+08</end_timestamp>
               <start_location xsi:type="xsd:string">Start location content</start_location>
               <end_location xsi:type="xsd:string">End location content</end_location>
               <trip_distance xsi:type="xsd:decimal">2784</trip_distance>
               <trip_time xsi:type="xsd:string">00:06:08</trip_time>
               <registration xsi:type="xsd:string">XYZ123</registration>
               <driver_id xsi:type="xsd:string">1234</driver_id>
               <driver xsi:type="xsd:string">driver content</driver>
               <driver_id_number xsi:type="xsd:string">12341234</driver_id_number>
               <client_vehicle_description xsi:type="xsd:string">Vehicle desc</client_vehicle_description>
            </item>
            <item xsi:type="tns:get_all_tripsResultType0Row">
               <vehicle_id xsi:type="xsd:decimal">1234</vehicle_id>
               <start_timestamp xsi:type="xsd:string">2017-03-19 16:51:09+08</start_timestamp>
               <end_timestamp xsi:type="xsd:string">2017-03-19 16:57:17+08</end_timestamp>
               <start_location xsi:type="xsd:string">Start location content</start_location>
               <end_location xsi:type="xsd:string">End location content</end_location>
               <trip_distance xsi:type="xsd:decimal">2784</trip_distance>
               <trip_time xsi:type="xsd:string">00:06:08</trip_time>
               <registration xsi:type="xsd:string">XYZ123</registration>
               <driver_id xsi:type="xsd:string">1234</driver_id>
               <driver xsi:type="xsd:string">driver content</driver>
               <driver_id_number xsi:type="xsd:string">12341234</driver_id_number>
               <client_vehicle_description xsi:type="xsd:string">Vehicle desc</client_vehicle_description>
            </item>
         </SqlResult0>
      </ns1:endpoint.get_all_tripsResponse>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

 

If I feed that data to Qlik as an XML file, Qlik automatically gives me a lovely flat table in which each row is an "item". Doing it this way Qlik generates a very neat little query which I modify slightly to yield roughly this.

[VehicleData]:
LOAD
	[vehicle_id],
	[start_timestamp],
	[end_timestamp],
	[start_location],
	[end_location],
	[trip_distance],
	[trip_time],
	[registration],
	[driver_id],
	[driver],
	[driver_id_number],
	[client_vehicle_description],
	[driver_id/xsi:nil]
 FROM [lib://datasource name]
(XmlSimple, table is [Envelope/Body/endpoint.get_all_tripsResponse/SqlResult0/item], URL is [$(i)])
WHERE trip_distance > 0

 

To access the data directly from the system, however, I need to use the REST connector so I can do authentication, set a bunch of headers and use a POST. 

When I do that Qlik takes a far more advanced approach to the XML . It treats each field as a table linked by generated keys. The query syntax that Qlik is generating is described in the help here but it is proving a bit beyond my ability to modify the query to get the same simple table of data that Qlik's file reader provides.

Does anyone know a way I might be able to get the same flat table of results that the XML file reader gives me but using the REST connection?

For what it's worth, this the query Qlik generates when I use "select data" from the REST connector in the data load editor. I've tried to modify it to yield a flat table result but I'm not sure how to go about that.

 

LIB CONNECT TO 'datasource name';

RestConnectorMasterTable:
SQL SELECT 
	"__KEY_Envelope",
	(SELECT 
		(SELECT 
			(SELECT 
				(SELECT 
					(SELECT 
						"@Content",
						"__FK_vehicle_id"
					FROM "vehicle_id" FK "__FK_vehicle_id" ContentFieldAlias "@Content"),
					(SELECT 
						"@Content" AS "@Content_u0",
						"__FK_start_timestamp"
					FROM "start_timestamp" FK "__FK_start_timestamp" ContentFieldAlias "@Content_u0"),
					(SELECT 
						"@Content" AS "@Content_u1",
						"__FK_end_timestamp"
					FROM "end_timestamp" FK "__FK_end_timestamp" ContentFieldAlias "@Content_u1"),
					(SELECT 
						"@Content" AS "@Content_u2",
						"__FK_start_location"
					FROM "start_location" FK "__FK_start_location" ContentFieldAlias "@Content_u2"),
					(SELECT 
						"@Content" AS "@Content_u3",
						"__FK_end_location"
					FROM "end_location" FK "__FK_end_location" ContentFieldAlias "@Content_u3"),
					(SELECT 
						"@Content" AS "@Content_u4",
						"__FK_trip_distance"
					FROM "trip_distance" FK "__FK_trip_distance" ContentFieldAlias "@Content_u4"),
					(SELECT 
						"@Content" AS "@Content_u5",
						"__FK_trip_time"
					FROM "trip_time" FK "__FK_trip_time" ContentFieldAlias "@Content_u5"),
					(SELECT 
						"@Content" AS "@Content_u6",
						"__FK_registration"
					FROM "registration" FK "__FK_registration" ContentFieldAlias "@Content_u6"),
					(SELECT 
						"@Content" AS "@Content_u7",
						"__FK_driver_id"
					FROM "driver_id" FK "__FK_driver_id" ContentFieldAlias "@Content_u7"),
					(SELECT 
						"@Content" AS "@Content_u8",
						"__FK_driver"
					FROM "driver" FK "__FK_driver" ContentFieldAlias "@Content_u8"),
					(SELECT 
						"@Content" AS "@Content_u9",
						"__FK_driver_id_number"
					FROM "driver_id_number" FK "__FK_driver_id_number" ContentFieldAlias "@Content_u9"),
					(SELECT 
						"@Content" AS "@Content_u10",
						"__FK_client_vehicle_description"
					FROM "client_vehicle_description" FK "__FK_client_vehicle_description" ContentFieldAlias "@Content_u10")
				FROM "item" PK "__KEY_item" FK "__FK_item")
			FROM "SqlResult0" PK "__KEY_SqlResult0" FK "__FK_SqlResult0")
		FROM "endpoint.get_all_tripsResponse" PK "__KEY_endpoint.get_all_tripsResponse" FK "__FK_endpoint.get_all_tripsResponse")
	FROM "Body" PK "__KEY_Body" FK "__FK_Body")
FROM XML "Envelope" PK "__KEY_Envelope";

[vehicle_id]:
LOAD	[@Content] AS [@Content]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_vehicle_id]);


[start_timestamp]:
LOAD	[@Content_u0] AS [@Content_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_start_timestamp]);


[end_timestamp]:
LOAD	[@Content_u1] AS [@Content_u1]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_end_timestamp]);


[start_location]:
LOAD	[@Content_u2] AS [@Content_u2]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_start_location]);


[end_location]:
LOAD	[@Content_u3] AS [@Content_u3]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_end_location]);


[trip_distance]:
LOAD	[@Content_u4] AS [@Content_u4]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_trip_distance]);


[trip_time]:
LOAD	[@Content_u5] AS [@Content_u5]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_trip_time]);


[registration]:
LOAD	[@Content_u6] AS [@Content_u6]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_registration]);


[driver_id]:
LOAD	[@Content_u7] AS [@Content_u7]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_driver_id]);


[driver]:
LOAD	[@Content_u8] AS [@Content_u8]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_driver]);


[driver_id_number]:
LOAD	[@Content_u9] AS [@Content_u9]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_driver_id_number]);


[client_vehicle_description]:
LOAD	[@Content_u10] AS [@Content_u10]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_client_vehicle_description]);


DROP TABLE RestConnectorMasterTable;
Labels (2)
1 Reply
Vu_Nguyen
Support (Former)
Support (Former)

Hi, 

Qilk REST connector does not yet support flattening nested XML/ JSON responses. There is an ongoing feature request for this as far as I know. This article https://support.qlik.com/articles/000074304 gives some more discussion (actually it's making a reference to your community post).

Hope it helps.

Vu Nguyen
If a post helps resolve your issue, please mark the answer as correct.