title: Generate XML file format from Oracle stored procedure
body: <p>I have created a stored procedure for generating XML files as per given input parameter "Table_Name".</p>
  
  <p>Stored procedure is:</p>
  
  <pre><code>create or replace 
  PROCEDURE Export_In_XML11JUNE 
  (
     V_TABLE_NAME1 IN varchar2,
     v_FLAG OUT NUMBER
    )
  AS
  BEGIN
               ----- Export  table data
            DECLARE
              v_file  UTL_FILE.file_type;
              qryCtx DBMS_XMLGEN.ctxHandle;
              result CLOB;
              v_FILENAME varchar2(50);
              xt_data xmltype;
              v_ctx dbms_xmlgen.ctxHandle;
              rc_data sys_refcursor;
              V_TABLE_NAME varchar2(50);
              cnt number :=0;
  
       BEGIN 
                       V_TABLE_NAME :=trim(UPPER(V_TABLE_NAME1));          
                             v_file := UTL_FILE.fopen('MYXML',V_TABLE_NAME ||'.xml', 'W');
  
                                OPEN rc_data FOR 
                                'select * FROM '||V_TABLE_NAME||' ORDER BY 1' ;
                                  v_ctx := dbms_xmlgen.newContext (rc_data);
                              DBMS_XMLGEN.USEITEMTAGSFORCOLL (v_ctx);
  
                                    DBMS_XMLGEN.SETNULLHANDLING(v_ctx, 1);
                                     DBMS_XMLGEN.setrowsettag(v_ctx,'root'); 
                                     DBMS_XMLGEN.setrowtag(v_ctx,V_TABLE_NAME );
                                     result:=  DBMS_XMLGEN.getXML(v_ctx);
                                     result := REPLACE( result, '&lt;?xml version="1.0"?&gt;','&lt;?xml version="1.0" encoding="UTF-8" standalone ="yes"?&gt;');
                                     -- DBMS_XMLGEN.RESTARTQUERY (v_ctx);   
                                   --  xt_data := dbms_xmlgen.getXMLType (v_ctx);
  
                                     dbms_xslprocessor.clob2file( result, 'MYXML', ''||V_TABLE_NAME||'.xml',871);
                                      dbms_xmlgen.closeContext (v_ctx);
                                      v_FLAG := 1;
  
                              EXCEPTION
                              WHEN OTHERS THEN
                                   DBMS_OUTPUT.PUT_LINE(SQLERRM);
                                   dbms_xmlgen.closeContext (v_ctx);
                                   v_FLAG := 0;
  
  
                            END ;
  
  END Export_In_XML11JUNE;
  </code></pre>
  
  <p>and it returns XML in this format:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" standalone ="yes"?&gt;
  &lt;root xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"&gt;
   &lt;AGNCY&gt;
    &lt;AGENCYID&gt;0000&lt;/AGENCYID&gt;
    &lt;NAME&gt;ABC&lt;/NAME&gt;
    &lt;MARKDELETED&gt;0&lt;/MARKDELETED&gt;
   &lt;/AGNCY&gt;
   &lt;AGNCY&gt;
  ....
  ....
  </code></pre>
  
  <p>and I required XML out put is </p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" standalone ="yes"?&gt;
  &lt;root xsi:noNamespaceSchemaLocation="c:\data\Agency.xsd" xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"&gt;
   &lt;AGNCY&gt;
    &lt;AGENCYID&gt;0000&lt;/AGENCYID&gt;
    &lt;NAME&gt;Caltrans&lt;/NAME&gt;
    &lt;MARKDELETED&gt;0&lt;/MARKDELETED&gt;
   &lt;/AGNCY&gt;
   &lt;AGNCY&gt;
  ....
  ....
  ....
  </code></pre>
  
  <p>I want  xsi:noNamespaceSchemaLocation="c:\data\table_Name.xsd"  line in root tag.</p>
  
  <p>Here <code>table_Name</code> is input parameter for the stored procedure.  </p>
  
  <p>I have tried but not found any solution. Please guide me. </p>