marchello
Posts: 81
Joined: Fri Oct 11, 2013 8:59 am

join two xmltables

Wed Oct 12, 2016 3:11 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"

marchello
Posts: 81
Joined: Fri Oct 11, 2013 8:59 am

Re: join two xmltables

Wed Oct 12, 2016 4:32 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";

Heater
Posts: 16332
Joined: Tue Jul 17, 2012 3:02 pm

Re: join two xmltables

Thu Oct 13, 2016 7:50 am

I'm curious. What language/system is that used in?

Never seen anything like it. It looks like a nightmare, somehow that manages to mix up SQL, XML and JSON in a horrible mess.

I'm wondering if it's embedded in PHP which is on turn embedded in HTML.

Any chance we could see some of the surrounding code that is used from?
Memory in C++ is a leaky abstraction .

marchello
Posts: 81
Joined: Fri Oct 11, 2013 8:59 am

Re: join two xmltables

Thu Oct 13, 2016 9:42 am

No chance to reveal any name or surrounding code, I'm sorry (confidentiality agreement).

Just BTW, sql + xml and sql + json is not a big deal, you can easily find bunch of examples on stackoverflow etc.
I agree that sql + webservices in the same environment is something new to see.

Heater
Posts: 16332
Joined: Tue Jul 17, 2012 3:02 pm

Re: join two xmltables

Thu Oct 13, 2016 10:12 am

That's a shame.
...sql + xml and sql + json is not a big deal,...
Perhaps not.

Much of the time one does not need all the features an SQL offers so getting rid of it can make life much easier. Hence the rise of NoSQL databases in recent years.

XML is horrible, complex and slow to deal with. Hence the rise of doing everything in JSON in recent years.

Mixing up SQL, XML and JSON just seems crazy to me.
Memory in C++ is a leaky abstraction .

marchello
Posts: 81
Joined: Fri Oct 11, 2013 8:59 am

Re: join two xmltables

Thu Oct 13, 2016 10:27 am

Well, it is up to you how you feel about it, if you think it is crazy then ok.
But if you try to work with webservices, you'll see they return data in xml or json format (sometimes it is plain text of course), so you should deal with it. BTW, nosql just allows to store and deal with data that has no strict structure, it doesn't mean that nosql allows to get rid of xml/json/etc. I began to work with nosql too..
In short, if someone wants to earn money in this area, then it is better to learn it.

ghans
Posts: 7882
Joined: Mon Dec 12, 2011 8:30 pm
Location: Germany

Re: join two xmltables

Thu Oct 13, 2016 10:43 am

If somebody wants to earn money in this area , they should know how to use their propietary, unknown and top-secret
tools instead asking for free support in public from volunteers. Is your vendor refusing to support his product ?

ghans
Last edited by ghans on Thu Oct 13, 2016 10:50 am, edited 1 time in total.
• Don't like the board ? Missing features ? Change to the prosilver theme ! You can find it in your settings.
• Don't like to search the forum BEFORE posting 'cos it's useless ? Try googling : yoursearchtermshere site:raspberrypi.org

Heater
Posts: 16332
Joined: Tue Jul 17, 2012 3:02 pm

Re: join two xmltables

Thu Oct 13, 2016 10:44 am

Very true. If that's what you have to do then that is what you have to do.

Certainly SQL is not going away. It's a crucial technology for storing structured data reliably and consistently.

Neither is XML. There is a lot of that about.

I was just commenting on trends as I see them.

I did not mean to imply that SQL or XML are crazy in themselves. Just that mixing them and JSON all together seems to be getting way over complicated. Like that mess of PHP, HTML, SQL, JS one sees in the same source file in PHP based web servers.
Memory in C++ is a leaky abstraction .

marchello
Posts: 81
Joined: Fri Oct 11, 2013 8:59 am

Re: join two xmltables

Thu Oct 13, 2016 10:52 am

Asking for help from vendor is good idea, I do it on some stage too. Asking this in public is what I do after hours of thinking... And it just helps to compile my thoughts on single sheet. I can do it in my favourite editor instead, lol. In fact, I almost answered my question myself. Thanks to all anyway )

Heater
Posts: 16332
Joined: Tue Jul 17, 2012 3:02 pm

Re: join two xmltables

Thu Oct 13, 2016 11:21 am

So we are never going to find out what environment that crazy syntax is for ?
Memory in C++ is a leaky abstraction .

marchello
Posts: 81
Joined: Fri Oct 11, 2013 8:59 am

Re: join two xmltables

Thu Oct 13, 2016 11:24 am

Nope, so sorry (confidentiality agreement).

Heater
Posts: 16332
Joined: Tue Jul 17, 2012 3:02 pm

Re: join two xmltables

Thu Oct 13, 2016 1:10 pm

So that's a bit of a waste of our time then isn't it. For those of us who get curious about things posted on public forums.
Memory in C++ is a leaky abstraction .

Return to “General programming discussion”