You seem to have a complex recursive nature to the XML ? In any event, here's a couple of examples to get you going, but there's plenty of good examples via google and asktom on how to use xmltable to drill down into what you need.
SQL> drop table t1 purge;
Table dropped.
SQL>
SQL> create table t1 ( x xmltype );
Table created.
SQL>
SQL> insert into t1 values (
2 xmltype('<?xml version="1.0" encoding="UTF-8"?>
3 <G1>
4 <R>
5 <S1><D A="Heading_1" />
6 <S6>
7 No Data Found
8 </S6>
9 </S1></R>
10 <R>
11 <S1><D A="Heading_2" />
12 <G1>
13 <R>
14 <S1><D A="Department1" />
15 <G1>
16 <R><D A="3" B="61" C="3" /></R>
17 <R><D A="3" B="63" C="1" /></R></G1>
18 </S1></R>
19 <R>
20 <S1><D A="Department2" />
21 <G1>
22 <R><D A="3" B="37" C="1" /></R>
23 <R><D A="3" B="61" C="2" /></R></G1>
24 </S1></R>
25 <R>
26 <S1><D A="Department3" />
27 <G1>
28 <R><D A="3" B="61" C="2" /></R></G1>
29 </S1></R>
30 <R>
31 <S1><D A="Department4" />
32 <G1>
33 <R><D A="3" B="61" C="2" /></R></G1>
34 </S1></R>
35 <R>
36 <S1><D A="Department5" />
37 <G1>
38 <R><D A="3" B="37" C="1" /></R>
39 <R><D A="3" B="61" C="4" /></R>
40 <R><D A="3" B="63" C="1" /></R></G1>
41 </S1></R>
42 <R>
43 <S1><D A="Department6" />
44 <G1>
45 <R><D A="3" B="61" C="2" /></R></G1>
46 </S1></R>
47 <R>
48 <S1><D A="Department7" />
49 <G1>
50 <R><D A="3" B="61" C="2" /></R></G1>
51 </S1></R>
52 <R>
53 <S1><D A="Department8" />
54 <G1>
55 <R><D A="3" B="37" C="1" /></R>
56 <R><D A="3" B="61" C="3" /></R>
57 <R><D A="3" B="63" C="1" /></R></G1>
58 </S1></R>
59 <R>
60 <S1><D A="Department9" />
61 <G1>
62 <R><D A="3" B="61" C="2" /></R></G1>
63 </S1></R>
64 <R>
65 <S1><D A="Department10" />
66 <G1>
67 <R><D A="3" B="61" C="2" /></R></G1>
68 </S1></R>
69 <R>
70 <S1><D A="Department11" />
71 <G1>
72 <R><D A="3" B="61" C="2" /></R></G1>
73 </S1></R>
74 <R>
75 <S1><D A="Department12" />
76 <G1>
77 <R><D A="3" B="61" C="2" /></R></G1>
78 </S1></R>
79 <R>
80 <S5>
81 <G1>
82 <R><D A="3" B="37" C="3" /></R>
83 <R><D A="3" B="61" C="28" /></R>
84 <R><D A="3" B="63" C="3" /></R></G1>
85 </S5></R></G1>
86 </S1></R></G1>'));
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select c1, c2, c3, c4
2 from t1 ,
3 xmltable('/G1/R/S1'
4 passing
5 t1.x
6 columns c1 varchar2(15) path 'S6'
7 ,c2 varchar2(15) path 'D/@A'
8 ,c3 varchar2(15) path 'D/@B'
9 ,c4 varchar2(15) path 'D/@C'
10 );
C1 C2 C3 C4
--------------- --------------- --------------- ---------------
Heading_1
No Data Found
Heading_2
SQL>
SQL>
SQL> select c2, c3, c4
2 from t1 ,
3 xmltable('for $i in $p//*/D
4 return $i'
5 passing
6 t1.x as "p"
7 columns c2 varchar2(15) path '@A'
8 ,c3 varchar2(15) path '@B'
9 ,c4 varchar2(15) path '@C'
10 );
C2 C3 C4
--------------- --------------- ---------------
Heading_1
Heading_2
Department1
3 61 3
3 63 1
Department2
3 37 1
3 61 2
Department3
3 61 2
Department4
3 61 2
Department5
3 37 1
3 61 4
3 63 1
Department6
3 61 2
Department7
3 61 2
Department8
3 37 1
3 61 3
3 63 1
Department9
3 61 2
Department10
3 61 2
Department11
3 61 2
Department12
3 61 2
3 37 3
3 61 28
3 63 3
35 rows selected.
SQL>
SQL>
SQL>