title: Retrieve xml node value using xmltable 11g for multi items
body: <p>Hope this is very simple, but I am missing something.</p>
  
  <p>This is my XML :</p>
  
  <pre><code> &lt;?xml version = '1.0' encoding = 'UTF-8'?&gt;&lt;PFA&gt;
         &lt;Person id="11" action="add" date="20-Nov-2012"&gt;
            &lt;Gender&gt;Male&lt;/Gender&gt;
            &lt;ActiveStatus&gt;Active&lt;/ActiveStatus&gt;
            &lt;Deceased&gt;Yes&lt;/Deceased&gt;
              &lt;RoleDetail&gt;
               &lt;Roles RoleType="Primary Occupation"&gt;
                  &lt;OccTitle OccCat="16"&gt;Deceased&lt;/OccTitle&gt;
               &lt;/Roles&gt;
               &lt;Roles RoleType="Previous Roles"&gt;
                  &lt;OccTitle SinceMonth="Nov" SinceYear="2010" ToMonth="Jan" ToYear="2011" OccCat="16"&gt;Candidate&lt;/OccTitle&gt;
                  &lt;OccTitle SinceMonth="Mar" SinceYear="2005" ToYear="2005" OccCat="16"&gt;Candidate,23&lt;/OccTitle&gt;
                  &lt;OccTitle OccCat="16"&gt;President&lt;/OccTitle&gt;
                  &lt;OccTitle SinceDay="22" SinceMonth="Oct" SinceYear="1993" ToDay="15" ToMonth="Mar" ToYear="2003" OccCat="1"&gt;President &lt;/OccTitle&gt;
                  &lt;OccTitle OccCat="7"&gt;Supreme Commander&lt;/OccTitle&gt;
                  &lt;OccTitle SinceDay="08" SinceMonth="Dec" SinceYear="1976" ToDay="14" ToMonth="Jul" ToYear="1978" OccCat="1"&gt;Prime Minister &lt;/OccTitle&gt;
               &lt;/Roles&gt;
            &lt;/RoleDetail&gt;
          &lt;/Person&gt;
        &lt;/PFA&gt;
  </code></pre>
  
  <hr>
  
  <p>I am querying this xml to store the values in my table.</p>
  
  <p>My Query -</p>
  
  <pre><code>SELECT t.personid, t.occtitle,r.roleid,t.sinceday,t.sincemonth,t.sinceyear,t.today,t.tomonth,t.toyear,t.occcat
  FROM xml_files p,master_roletypelists r,
       XMLTable(
        'for $i in PFA/Person/RoleDetail/Roles/OccTitle
         return &lt;row&gt;
         {
            $i/../../../@id,
            $i/../@RoleType,
            $i/@SinceDay,
            $i/@SinceMonth,
            $i/@SinceYear,
            $i/@ToDay,
            $i/@ToMonth,
            $i/@ToYear,
            $i/@OccCat
         } 
         &lt;/row&gt;' 
        PASSING p.filecontent
        COLUMNS 
                personid  number PATH '@id',
                occtitle    VARCHAR2(4000)  PATH '.',
                RoleType    VARCHAR2(2000)  PATH '@RoleType',
                sinceday    VARCHAR2(2000)  PATH '@SinceDay',
                sincemonth    VARCHAR2(2000)  PATH '@SinceMonth',
                sinceyear    VARCHAR2(2000)  PATH '@SinceYear',
                today    VARCHAR2(2000)  PATH '@ToDay',
                tomonth    VARCHAR2(2000)  PATH '@ToMonth',
                toyear    VARCHAR2(2000)  PATH '@ToYear',
                occcat    VARCHAR2(2000)  PATH '@OccCat'
  
       ) t where t.roletype = r.rolename
  ;
  </code></pre>
  
  <p>But for this I am getting the column 'OCCTITLE' as null.</p>
  
  <p>Please help me to modify my query to get the OccTitle node value as well.
  For example in OccTitle column in first row I am supposed to get the value "Deceased" instead of null.</p>
  
  <p>If this is not the way to do this please help me to correct it.
  Any help would be appreciated.</p>