PL/SQL unique column identification from two table

Question

I am using Oracle 11G. I have a PL/SQL procedure which is reading XML using xquery from XMLTYPE column in a table. The XML contains data of DEPARTMENT and its SECTIONS. DEPARTMENT has one to many relationship with SECTIONS i.e. a DEPARTMENT can have one or multiple SECTIONS and there may be instances where DEPARTMENT will not have any SECTIONS.

Structure of XML is such that <DATA> tag identifies a DEPARTMENT and set of its corresponding SECTIONS.

XML

<ROWSET> 
<DATA>
 <DEPARTMENT>
  <DEPARTMENT_ID>DEP1</DEPARTMENT_ID>
  <DEPARTMENT_NAME>myDEPARTMENT1</DEPARTMENT_NAME>
 </DEPARTMENT>
 <SECTIONS>
  <SECTIONS_ID>6390135666643567</SECTIONS_ID>
  <SECTIONS_NAME>mySection1</SECTIONS_NAME>
  </SECTIONS>
   <SECTIONS>
  <SECTIONS_ID>6390135666643567</SECTIONS_ID>
  <SECTIONS_NAME>mySection2</SECTIONS_NAME>
  </SECTIONS>
 </DATA>
 <DATA>
 <DEPARTMENT>
  <DEPARTMENT_ID>DEP2</DEPARTMENT_ID>
  <DEPARTMENT_NAME>myDEPARTMENT2</DEPARTMENT_NAME>
 </DEPARTMENT>
 <SECTIONS>
  <SECTIONS_ID>63902</SECTIONS_ID>
  <SECTIONS_NAME>mySection1</SECTIONS_NAME>
  </SECTIONS>
 </DATA>
<DATA>
 <DEPARTMENT>
  <DEPARTMENT_ID>DEP3</DEPARTMENT_ID>
  <DEPARTMENT_NAME>myDEPARTMENT3</DEPARTMENT_NAME>
 </DEPARTMENT>
</DATA>
</ROWSET>

Query which i am using is:-

  select d.department_id
       , d.department_name
       , s.sections_id
      , s.sections_name
   from xml_unit_download t
     , xmltable(
         '/ROWSET/DATA'
         passing t.xml_file
         columns
           DEPARTMENT_ID   varchar2(20) path 'DEPARTMENT/DEPARTMENT_ID'
         , DEPARTMENT_NAME varchar2(30) path 'DEPARTMENT/DEPARTMENT_NAME'
         , SECTIONS        xmltype      path 'SECTIONS'
       ) d
     , xmltable(
         '/SECTIONS'
         passing d.sections
         columns
           SECTIONS_ID     varchar2(20) path 'SECTIONS_ID'
        , SECTIONS_NAME   varchar2(30) path 'SECTIONS_NAME'
      ) s
 where
  t.Status = 7

Now this Xquery does not fetch the result of DEP3, how will i cater this?

-------------UPDATED-------------
Expected Output

DEPARTMENT_ID        DEPARTMENT_NAME                SECTIONS_ID          SECTIONS_NAME
-------------------- ------------------------------ -------------------- 
DEP1                 myDEPARTMENT1                  6390135666643567     mySection1
DEP1                 myDEPARTMENT1                  6390135666643567     mySection2
DEP2                 myDEPARTMENT2                  63902                mySection1
DEP3                 myDEPARTMENT3

Can you please show what your expected output should be? – OldProgrammer May 02 '13 at 11:46 — OldProgrammer, May 02 '13 at 11:46
@OldProgrammer Please see updated expected output, thanks – Femme Fatale May 02 '13 at 12:15 — Femme Fatale, May 02 '13 at 12:15

score 2 · Accepted Answer · answered May 02 '13 at 12:22

You can make them outer joins:

...
   from xml_unit_download t
     , xmltable(
... 
       ) (+) d
     , xmltable(
...
      ) (+) s
...

which gives:

DEPARTMENT_ID        DEPARTMENT_NAME                SECTIONS_ID          SECTIONS_NAME
-------------------- ------------------------------ -------------------- ------------------------------
DEP1                 myDEPARTMENT1                  6390135666643567     mySection1
DEP1                 myDEPARTMENT1                  6390135666643567     mySection2
DEP2                 myDEPARTMENT2                  63902                mySection1
DEP3                 myDEPARTMENT3

PL/SQL unique column identification from two table

1 Answers1