CREATE TABLE TEST_XML
(
  DOC_ID   NUMBER(10),
  DOC_XML  SYS.XMLTYPE
);

Insert into TEST_XML
   (DOC_ID, DOC_XML)
 Values
   (1, XMLTYPE('<doc>
  <id>1</id>
  <store>
    <id>STR1</id>
    <dept>
      <id>DEPT1_STR1</id>
      <file_name>dept1 FOR store1</file_name>
    </dept>
    <dept>
      <id>DEPT2_STR1</id>
      <file_name>dept2 FOR store1</file_name>
    </dept>
    <id>STR2</id>
    <dept>
      <id>DEPT1_STR2</id>
      <file_name>dept1 FOR store2</file_name>
    </dept>
    <dept>
      <id>DEPT2_STR2</id>
      <file_name>dept2 FOR store2</file_name>
    </dept>
  </store>
</doc>
'));
Insert into TEST_XML
   (DOC_ID, DOC_XML)
 Values
   (2, XMLTYPE('<doc>
  <id>2</id>
  <store>
    <id>STR2</id>
    <dept>
      <id>DEPT1_STR2</id>
      <file_name>dept1 FOR store2</file_name>
    </dept>
    <dept>
      <id>DEPT2_STR2</id>
      <file_name>FIR21 FOR store2</file_name>
    </dept>
  </store>
</doc>
'));
COMMIT;

DOC_ID	STORE_ID	DEPT_ID

1	STR1		DEPT1_STR1
1	STR1		DEPT2_STR1
1	STR2		DEPT1_STR2
1	STR2		DEPT2_STR2
2	STR2		DEPT1_STR2
2	STR2		DEPT2_STR2

select extractValue(a.doc_xml,'/doc/id') as doc_id, 
--extractValue(value(dept),'/doc/store/id') as store_id, 
extractValue(value(dept),'/dept/id') as dept_id 
from test_xml a, 
table(XMLSequence(extract(a.doc_xml,'/doc/store/dept'))) dept

select extractValue(a.doc_xml,'/doc/id') as doc_id, 
extractValue(value(str),'/id') as store_id
from test_xml a,
table(XMLSequence(extract(a.doc_xml,'/doc/store/id'))) str