title: Procedure for loading XML in oracle table using XMLType
body: <p>I am inspired with a case of xml type procedure for parsing and insert data in a table here. In my case the table already exist and once the xml is generate by an external app i need load data in the table so i created my procedure and when i compiled all fine but at the execution i have mistakes.
  So here is my procedure with xml inside.</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE dta_proc AS 
  l_xml xmltype;
  l_val VARCHAR2(10000) := '&lt;root&gt;
    &lt;record&gt;
    &lt;id_localisation&gt;8PJ&lt;/id_localisation&gt;
    &lt;data&gt;
     &lt;id_client&gt;50C&lt;/id_client&gt;  
        &lt;mail&gt;1@mail.com&lt;/mail&gt;
        &lt;adress&gt;10  &lt;/adress&gt;
        &lt;num_tel&gt;001&lt;/num_tel&gt;
        &lt;key&gt;C&lt;/key&gt;
       &lt;contact&gt;
          &lt;name&gt;toto&lt;/name&gt;
          &lt;birth&gt;01/30/009&lt;/birth&gt;
          &lt;city&gt;London&lt;/city&gt;
        &lt;/contact&gt;
    &lt;/data&gt; 
    &lt;data&gt;
    &lt;id_client&gt;25C&lt;/id_client&gt; 
        &lt;mail&gt;2@gmaiil.com&lt;/mail&gt;
        &lt;adress&gt;20&lt;/adress&gt;
        &lt;num_tel&gt;02200&lt;/num_tel&gt;
        &lt;key&gt;D1&lt;/key&gt;
        &lt;contact&gt;
          &lt;name&gt;tata&lt;/name&gt;
          &lt;birth&gt;02/08/2004&lt;/birth&gt;
          &lt;city&gt;Spa&lt;/city&gt;
        &lt;/contact&gt;
    &lt;/data&gt; 
  &lt;/record&gt;
    &lt;record&gt;
    &lt;id_localisation&gt;ESP31&lt;/id_localisation&gt;
    &lt;data&gt;
     &lt;id_client&gt;70D&lt;/id_client&gt;  
          &lt;mail&gt;3@gmail.com&lt;/mail&gt;
          &lt;adress&gt;7Bcd&lt;/adress&gt;
          &lt;num_tel&gt;5555&lt;/num_tel&gt;
          &lt;key&gt;D2&lt;/key&gt;
        &lt;contact&gt;
          &lt;name&gt;titi&lt;/name&gt;
          &lt;birth&gt;05/07/2014&lt;/birth&gt;
          &lt;city&gt;StMarine&lt;/city&gt;
        &lt;/contact&gt;
    &lt;/data&gt;
    &lt;data&gt;
          &lt;id_client&gt;10D&lt;/id_client&gt;
          &lt;mail&gt;4@gmail.com&lt;/mail&gt;
          &lt;adress&gt;888&lt;/adress&gt;
          &lt;num_tel&gt;881.0&lt;/num_tel&gt;
          &lt;key&gt;D3&lt;/key&gt;
        &lt;contact&gt;
          &lt;name&gt;awk&lt;/name&gt;
          &lt;birth&gt;05/08/1999&lt;/birth&gt;
          &lt;city&gt;Bahrein&lt;/city&gt;
        &lt;/contact&gt;
    &lt;/data&gt;
   &lt;/record&gt;
   &lt;/root&gt;';
       statut number;
       id_client varchar2(13);
       date_ev date;
       id_diff  varchar2(13);
  BEGIN
       l_xml        := xmltype(l_val);
  
       FOR x IN
       (SELECT VALUE(p) col_val
       FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml, '/begin/entry'))) p
       )
       loop
  
  IF x.col_val.existsnode('/root/record/id_localisation//text()') &gt; 0 THEN
           localisation := x.col_val.extract('/root/record/id_localisation//text()').getstringval();
  END IF;
  if x.col_val.existsnode('/root/record/data/id_client/text()') &gt; 0 then
       id_client := x.col_val.extract('/root/record/data/id_client/text()').getstringval();
  end if;
  if x.col_val.existsnode('/root/record/data/num_tel/text()') &gt; 0 then
       num_tel := x.col_val.extract('/root/record/data/num_tel/text()').getstringval();
  end if;
  IF x.col_val.existsnode('/root/record/data/contact/city/text()') &gt; 0 THEN
           city := x.col_val.extract('/root/record/data/contact/city/text()').getstringval();
  end if;
  INSERT INTO Customer
       (
            Loca,
            ID_CLT,
            TEL,
            Town
       )
       VALUES
       (
            localisation,
            id_client,
            num_tel,
            city
       );
       localisation := null;
       id_client := null;
       num_tel := null;
       city := null;
       end loop;
  commit;
  end  dta_proc;
  </code></pre>
  
  <p>Error when executing:</p>
  
  <p>execute xml_procedure;</p>
  
  <pre><code>ORA-31011: XML parsing failed
  ORA-19202: Error occurred in XML processing
  LPX-00245: extra data after end of document
  Error at line 26
  ORA-06512: at "SYS.XMLTYPE", line 310
  ORA-06512: at "xml_procedure", line 58
  ORA-06512: at line 1
  31011. 00000 -  "XML parsing failed"
  *Cause:    XML parser returned an error while trying to parse the document.
  *Action:   Check if the document to be parsed is valid.
  </code></pre>
  
  <p>One more question. How is it possible to automate the loading of the xml in my procedure once it is generated?</p>