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
Comments
Post a Comment