Enjoy. A CTE, Xpath, and Oracle-specific hierarchical bits for a data report I'm generating. My favorite part was the XML in a CLOB column where all the values were stored in XML element attributes. I tend to build these monstrosities up in a very repl-like way. SQL actually led me to Lisp . . .
BTW, I can't imagine any of the below is legible, but I find myself enjoying these types of things.
with bgu as
(select
protocol_code,
application_code,
description,
lab,
proj."seqno",
proj."project",
proj."protocol",
'false' "nexttag",
'true' "materialtag"
from
protocol,
xmltable('for $a in /*
for $n at $nidx in //*/MATERIAL
return <result>
<project>{fn:data($n/@project)}</project>
<protocol>{fn:data($n/@protocol)} </protocol>
</result>'
passing xmltype(protocol.xml)
columns
"seqno" for ordinality,
"project" varchar2(100),
"protocol" varchar2(100)) proj
where
active = 'Y'
union
select
protocol_code,
application_code,
description,
lab,
proj."seqno",
proj."project",
proj."protocol",
'true' "nexttag",
'false' "materialtag"
from
protocol,
xmltable('for $a in /*
for $n at $nidx in //*/NEXT
return <result>
<project>{fn:data($n/@project)}</project>
<protocol>{fn:data($n/@protocol)}</protocol>
</result>'
passing xmltype(protocol.xml)
columns
"seqno" for ordinality,
"project" varchar2(100),
"protocol" varchar2(100)) proj
where
active='Y'
order by 1,2,4 )
select
*
from
(select distinct
protocol_code,
sys_connect_by_path(protocol_code,'||||'),
connect_by_isleaf "isleaf"
from bgu
where protocol_code like 'PROTNAME%'
connect by nocycle prior "protocol" = protocol_code)
where "isleaf"=1
XML and Oracle drives me crazy in general. You wind up with neither a relational model nor a generally useful way of data storage/exchange.
I am almost always working with legacy schemas, many generated by long-extinct tools or developers, and being a genuinely curious person, wandering down strange code paths to see if I can satisfy random reporting requests.
I'm just biting my time until someone makes a "Daily WTF" website for monster queries. My worst was this sync that took data from one database, and put it into another one. The thing though is the source database was a CRM system that is on its 3rd iteration. So there's a million random fields from old systems that have accumulated over the years that need to be checked.
BTW, I can't imagine any of the below is legible, but I find myself enjoying these types of things.