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

crossroadsit — Tue, 21 Dec 2021 14:01:50 GMT

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;

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

Vu_Nguyen — Tue, 21 May 2019 12:13:59 GMT

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.

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

p_verkooijen — Tue, 05 Jul 2022 14:01:09 GMT

Have you managed to create a workaround, I've submitted this as a "new" idea https://community.qlik.com/t5/Suggest-an-Idea/REST-Connector-support-for-nested-JSON-and-XML-response/idi-p/1951867#M9898

topic Re: Can I query nested XML data (over REST) such that I get a flat table? in Connectivity & Data Prep

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

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

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