join two xmltables

Questions about XML that are not covered by the other forums should go here.
marco
Posts: 2

join two xmltables

Wed Oct 12, 2016 6:20 pm

Hi all,

I got some data returned by web api in json format. It is list of regions with timezone id.
I can select regions and timezone id (the path is /root/regions) in one sql request. I can select timezone id with timezone description (the path is /root/timezones) in other sql request as well. The api used for both sql request is the same.
The question is how do I join these two sql request into one? I'm not familiar with syntaxis so far. Please help, requests below include some "black box", because I got function that uses external webservice. Any help to perform join is appreciated.

Code: Select all

    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"


Code: Select all

    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"
marco
Posts: 2

Re: join two xmltables

Thu Oct 13, 2016 12:47 pm

Ok, I figured out how to join, but my code looks ugly. Now my question is how to optimize code below, because I use the same subquery twice

Code: Select all

    (exec "webservice1".invokeHTTP(endpoint=>'2.0/api1',requestHeaders=> 'App-Key: key1',action=>'GET',requestContentType=>'application/xml'))


Code: Select all

    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";

Return to “General XML Questions”

Who is online

Users browsing this forum: No registered users and 2 guests