sqlxmloracle-databasejoinoracle18c

Succinct way to join between XML queries?

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 query the same underlying view: gdb_items_vw.

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?

Solution

  • There are multiple solutions including:

    1. Using sub-query factoring clauses:

      WITH domain AS (<domain_query>),
subtype AS (<subtype_query>)
SELECT ...
FROM   domain LEFT OUTER JOIN subtype ON (...);

    2. Using subqueries:

      SELECT ...
FROM   (<domain_query>)
       LEFT OUTER JOIN (<subtype_query>)
       ON (...);

    3. 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.).