Oracle 18c:
db<>fiddle with sample data.
(1) I have a query that extracts domain data from an XML clob column:
select
substr(i.name ,0,17) as domain_name,
substr(x.code ,0,13) as domain_code,
substr(x.description,0,35) as domain_description
from
gdb_items_vw i
cross apply xmltable(
'/GPCodedValueDomain2/CodedValues/CodedValue'
passing xmltype(i.definition)
columns
code varchar2(255) path './Code',
description varchar2(255) path './Name'
) x
where
i.name in('ACTIVITY_ATN_1','ACTIVITY_GCSM_1','ACTIVITY_MS_2')
and i.name is not null
DOMAIN_NAME DOMAIN_CODE DOMAIN_DESCRIPTION
----------------- ------------- -----------------------------------
ACTIVITY_ATN_1 RECON_S RECONSTRUCT SIDEWALK
ACTIVITY_ATN_1 RECON_T RECONSTRUCT TRAIL
ACTIVITY_GCSM_1 CON_GCSM CONSTRUCT GCSM
ACTIVITY_GCSM_1 RECON_GCSM RECONSTRUCT_GCSM
ACTIVITY_MS_2 M LIFT AND SWING BRIDGE MAINTENANCE
ACTIVITY_MS_2 C BRIDGE CLEANING
(2) And I have a query that extracts subtype data from the same XML clob column:
select
substr(i.name ,0,18) as object_name,
substr(x.subtype_code ,0,12) as subtype_code,
substr(x.subtype_description,0,35) as subtype_description,
substr(x.subtype_field ,0,15) as subtype_field,
substr(x.subtype_domain ,0,20) as subtype_domain
from
gdb_items_vw i
cross apply xmltable(
'/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"]'
passing xmltype(i.definition)
columns
subtype_code number(38,0) path './../../SubtypeCode',
subtype_description varchar2(255) path './../../SubtypeName',
subtype_field varchar2(255) path './FieldName',
subtype_domain varchar2(255) path './DomainName'
) x
where
i.name is not null
and i.name = 'INFRASTR.BC_EVENTS'
OBJECT_NAME SUBTYPE_CODE SUBTYPE_DESCRIPTION SUBTYPE_FIELD SUBTYPE_DOMAIN
------------------ ------------ ----------------------------------- --------------- --------------------
INFRASTR.BC_EVENTS 0 ACTIVE TRANSPORTATION ACTIVITY ACTIVITY_ATN_1
INFRASTR.BC_EVENTS 1 GEODETIC CONTROL SURVEY MONUMENT ACTIVITY ACTIVITY_GCSM_1
INFRASTR.BC_EVENTS 2 MUNICIPAL STRUCTURES (BRIDGES) ACTIVITY ACTIVITY_MS_2
The domains have a different XML paths than the subtypes. So that's why I think I need separate queries for each, even though they both come from the same underlying view: gdb_items_vw.
- Domain path:
/GPCodedValueDomain2/CodedValues/CodedValue - Subtype path:
/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"]
Goal:
I want to join from domain.domain_name to subtype.subtype_domain to get the following result:
DOMAIN_NAME DOMAIN_CODE DOMAIN_DESCRIPTION SUBTYPE_CODE
----------------- ------------- ----------------------------------- ------------
ACTIVITY_ATN_1 RECON_S RECONSTRUCT SIDEWALK 0
ACTIVITY_ATN_1 RECON_T RECONSTRUCT TRAIL 0
ACTIVITY_GCSM_1 CON_GCSM CONSTRUCT GCSM 1
ACTIVITY_GCSM_1 RECON_GCSM RECONSTRUCT_GCSM 1
ACTIVITY_MS_2 M LIFT AND SWING BRIDGE MAINTENANCE 2
ACTIVITY_MS_2 C BRIDGE CLEANING 2
In other words, I want to bring the subtype_code column into the the domains query.
Question:
What's the most succinct way to join those XML queries? Is the only option to have two separate queries, and join them together via a left join?
CodePudding user response：
There are multiple solutions including:
Using sub-query factoring clauses:
WITH domain AS (<domain_query>), subtype AS (<subtype_query>) SELECT ... FROM domain LEFT OUTER JOIN subtype ON (...);Using subqueries:
SELECT ... FROM (<domain_query>) LEFT OUTER JOIN (<subtype_query>) ON (...);Directly referencing the tables without sub-queries:
SELECT substr(i1.name ,0,17) as domain_name, substr(x1.code ,0,13) as domain_code, substr(x1.description ,0,35) as domain_description, substr(i2.name ,0,18) as object_name, substr(x2.subtype_code ,0,12) as subtype_code, substr(x2.subtype_description,0,35) as subtype_description, substr(x2.subtype_field ,0,15) as subtype_field, substr(x2.subtype_domain ,0,20) as subtype_domain FROM gdb_items_vw i1 CROSS APPLY xmltable( '/GPCodedValueDomain2/CodedValues/CodedValue' passing xmltype(i1.definition) columns code varchar2(255) path './Code', description varchar2(255) path './Name' ) x1 LEFT OUTER JOIN ( gdb_items_vw i2 CROSS APPLY xmltable( '/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"]' passing xmltype(i2.definition) columns subtype_code number(38,0) path './../../SubtypeCode', subtype_description varchar2(255) path './../../SubtypeName', subtype_field varchar2(255) path './FieldName', subtype_domain varchar2(255) path './DomainName' ) x2 ) ON (substr(i1.name,0,17) = substr(x2.subtype_domain,0,20)) WHERE i1.name in('ACTIVITY_ATN_1','ACTIVITY_GCSM_1','ACTIVITY_MS_2') AND i2.name = 'INFRASTR.BC_EVENTS';
You can check the generated EXPLAIN PLANs but you will probably find that the SQL engine will generate similar (or, probably, even identical plans) and the result you get from all the options will be identical. Which one you use is personal preference based on other factors (readability, ease-of-maintenance, number of characters, etc.).
CodePudding user response：
As mentioned, the common key between the two queries is the "domain name", stored in domain.domain_name and subtype.subtype_domain.
One option might be to store the two separate queries in the WITH clause of a master query. Then in the body of the master query, do a simple left join to bring the subtype_code column into the the domains query.
with
domain as (...),
subtype as (...)
select
d.domain_name,
d.domain_code,
d.domain_description,
s.subtype_code
from
domain d
left join
subtype s
on d.domain_name = s.subtype_domain
DOMAIN_NAME DOMAIN_CODE DOMAIN_DESCRIPTION SUBTYPE_CODE
----------------- ------------- ----------------------------------- ------------
ACTIVITY_ATN_1 RECON_S RECONSTRUCT SIDEWALK 0
ACTIVITY_ATN_1 RECON_T RECONSTRUCT TRAIL 0
ACTIVITY_GCSM_1 CON_GCSM CONSTRUCT GCSM 1
ACTIVITY_GCSM_1 RECON_GCSM RECONSTRUCT_GCSM 1
ACTIVITY_MS_2 M LIFT AND SWING BRIDGE MAINTENANCE 2
ACTIVITY_MS_2 C BRIDGE CLEANING 2
db<>fiddle with sample data.