title: XML Oracle : Multiple Child Node extract
body: <p>I have an xml code :</p>
  
  <pre><code>&lt;begin&gt;
      &lt;entry&gt;
          &lt;lastname&gt;gordon&lt;/lastname&gt;
          &lt;NumberList&gt;
              &lt;number&gt;100&lt;/number&gt;
              &lt;codelist&gt;
                   &lt;code&gt;213&lt;/code&gt;
                   &lt;code&gt;214&lt;/code&gt;
              &lt;codelist&gt;
              &lt;login&gt;
                   &lt;user&gt;user1&lt;/user&gt;
                   &lt;user&gt;user2&lt;/user&gt;
              &lt;/login&gt;
          &lt;NumberList&gt;
          &lt;address&gt;
              &lt;addresslist&gt;Jl. jalan pelan-pelan ke Bekasi, Indonesia&lt;/addresslist&gt;
          &lt;/address&gt;
      &lt;/entry&gt;
      &lt;entry&gt;
          &lt;lastname&gt;mark&lt;/lastname&gt;
          &lt;address&gt;
              &lt;addresslist&gt;Jl. jalan cepet-cepet ke Jakarta, Indonesia&lt;/addresslist&gt;
          &lt;/address&gt;
      &lt;/entry&gt;
  &lt;/begin&gt;
  </code></pre>
  
  <p>my code:</p>
  
  <pre><code>FOR r IN (SELECT VALUE(p) col_val,
                   EXTRACT(VALUE(P), '/entry/codelist') AS code,
                   EXTRACT(VALUE(P), '/entry/login') AS login
             FROM TABLE(XMLSequence(Extract(x,'/begin/entry'))) p)
  LOOP
     IF r.col_val.existsnode('/entry/lastname/text()') &gt; 0 
     THEN
        vc_lastname := r.col_val.extract('/sdnEntry/lastname/text()').getstringval();
     END IF;
  
     IF r.col_val.existsnode('/entry/address/addresslist/text()') &gt; 0 
     THEN
      vc_address := r.col_val.extract('/sdnEntry/address/addresslist/text()').getstringval();
     END IF;
  
     IF r.col_val.existsnode('/entry/codelist/id/code/text()') &gt; 0 AND r.col_val.existsnode('/entry/login/user/text()') &gt; 0 
     THEN
        FOR R1 IN (SELECT EXTRACTVALUE(VALUE(T1), '/codelist/code/text()') AS code
                     FROM TABLE(XMLSEQUENCE(EXTRACT(R.code, '/codelist'))) T1)
        LOOP
           DBMS_OUTPUT.PUT_LINE(vc_uid||' - '||vc_firstName||' - '||R1.code||' - '||R2.address);
        END LOOP;
  
        FOR R2 IN (SELECT
                          EXTRACTVALUE(VALUE(T1), '/login/user/text()') AS user
                     FROM TABLE(XMLSEQUENCE(EXTRACT(R.address, 'login/'))) T1)
        LOOP
           DBMS_OUTPUT.PUT_LINE(vc_uid||' - '||vc_firstName||' - '||R2.user||' - '||R2.address);
        END LOOP;
    ELSE
          DBMS_OUTPUT.PUT_LINE(vc_uid||' - '||vc_firstName);
    END IF;
  </code></pre>
  
  <p>My problem : How to loop child nodes so the data will become like this :</p>
  
  <pre><code>LastName | Number | code    | user  |   address
  gordon   | 100    | 213     | user1 |Jl. jalan pelan-pelan ke Bekasi, Indonesia
  gordon   | 100    | 213     | user2 |Jl. jalan pelan-pelan ke Bekasi, Indonesia
  gordon   | 100    | 214     | user1 |Jl. jalan pelan-pelan ke Bekasi, Indonesia
  gordon   | 100    | 214     | user2 |Jl. jalan pelan-pelan ke Bekasi, Indonesia
  mark     | Null   | null    | null  |Jl. jalan cepet-cepet ke Jakarta, Indonesia
  </code></pre>
  
  <p>Any help would be appreciate.</p>