1

Topic: two xml tables

All greetings!
There is data in json a format which returns web api. The first request returns the list of regions with  a temporal zone (/root/regions). The second request returns the list  temporal zones with the description (/root/timezones). Api for two requests one same.
Question - how to connect two requests on  a temporal zone? "The black box" as function for reversal to api so high-grade sample code  I can not is used figures in request. Help .

SELECT
"xmlTable.idColumn";
"xmlTable.description";
"xmlTable.id";
"xmlTable.countryid";
"xmlTable.datetimeformatid";
"xmlTable.numberformatid";
"xmlTable.timezoneid"
FROM
(exec "webservice1".invokeHTTP (endpoint => ' 2.0/api1 ', requestHeaders => ' App-Key: key1 ', action =>'GET ', requestContentType =>'application/xml ')) w;
XMLTABLE (XMLNAMESPACES (' http://www.w3.org/2001/XMLSchema-instance ' as "xsi"),/root/regions ' PASSING JSONTOXML (' root ', to_chars (w.result, ' UTF-8 '))
COLUMNS
"idColumn" FOR ORDINALITY;
"description" STRING PATH ' description ';
"id" STRING PATH ' id ';
"countryid" STRING PATH ' countryid ';
"datetimeformatid" STRING PATH ' datetimeformatid ';
"numberformatid" STRING PATH ' numberformatid ';
"timezoneid" STRING PATH ' timezoneid'
) "xmlTable"
SELECT "xmlTable.idColumn", "xmlTable.id", "xmlTable.description", "xmlTable.offset" FROM
(exec "webservice1".invokeHTTP (endpoint => ' 2.0/api1 ', requestHeaders =>'App-Key: key1 ', action =>'GET ', requestContentType =>'application/xml ')) w;
XMLTABLE (XMLNAMESPACES (' http://www.w3.org/2001/XMLSchema-instance ' as "xsi"),/root/timezones ' PASSING JSONTOXML (' root ', to_chars (w.result, ' UTF-8 '))
COLUMNS
"idColumn" FOR ORDINALITY;
"id" STRING PATH ' id ';
"description" STRING PATH ' description ';
"offset" STRING PATH ' offset'
) "xmlTable"

2

Re: two xml tables

how to connect, only the code terrible. Now a question - how to optimize not to use twice the same subquery

(exec "webservice1".invokeHTTP (endpoint => ' 2.0/api1 ', requestHeaders => ' App-Key: key1 ', action =>'GET ', requestContentType =>'application/xml '))
SELECT
"xmlTable.idColumn";
"xmlTable.description";
"xmlTable.id";
"xmlTable.countryid";
"xmlTable.datetimeformatid";
"xmlTable.numberformatid";
"xmlTable.timezoneid";
"timeZones.description" as "timezonedescription";
"timeZones.offset"
FROM
(exec "webservice1".invokeHTTP (endpoint => ' 2.0/api1 ', requestHeaders => ' App-Key: key1 ', action =>'GET ', requestContentType =>'application/xml ')) w;
XMLTABLE (XMLNAMESPACES (' http://www.w3.org/2001/XMLSchema-instance ' as "xsi"),/root/regions ' PASSING JSONTOXML (' root ', to_chars (w.result, ' UTF-8 '))
COLUMNS
"idColumn" FOR ORDINALITY;
"description" STRING PATH ' description ';
"id" STRING PATH ' id ';
"countryid" STRING PATH ' countryid ';
"datetimeformatid" STRING PATH ' datetimeformatid ';
"numberformatid" STRING PATH ' numberformatid ';
"timezoneid" integer PATH ' timezoneid'
) "xmlTable";
(exec "webservice1".invokeHTTP (endpoint => ' 2.0/api1 ', requestHeaders =>'App-Key: key1 ', action =>'GET ', requestContentType =>'application/xml ')) w2;
XMLTABLE (XMLNAMESPACES (' http://www.w3.org/2001/XMLSchema-instance ' as "xsi"),/root/timezones ' PASSING JSONTOXML (' root ', to_chars (w2.result, ' UTF-8 '))
COLUMNS
"idColumn" FOR ORDINALITY;
"id" integer PATH ' id ';
"description" STRING PATH ' description ';
"offset" STRING PATH ' offset'
) "timeZones"
where "xmlTable". "timezoneid" = "timeZones". "id";