xml - PL/SQL unique column identification from two table -

i using oracle 11g. have pl/sql procedure reading xml using xquery xmltype column in table. xml contains data of department , sections. department has 1 many relationship sections i.e. department can have 1 or multiple sections , there may instances department not have sections.

structure of xml such <data> tag identifies department , set of 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 using is:-

  select d.department_id        , d.department_name        , s.sections_id       , s.sections_name    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    t.status = 7 

now xquery not fetch result of dep3, how 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 

you can make them outer joins:

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