PL SQL example for procedure to get XML, Update it and return.

Hi, Experts.
I need to write some procedure that will receive XML, analyze/parse it (take the Table Name from my MetaData table by using TableID that XML give me ) and return the Value of this table by using the code and Table name (Update the Value attribute in the incomming XML).

I have a MetaData Table like this:
__________________________________________
TableID    |  TableName   |   TableField   | Value  |
__________________________________________
1             |  Table1          |   Field1         | 1000   |
1             |  Table1          |   Field8         | 2345   |
2             |  Table2          |   Field2         | 2000   |
3             |  Table3          |   Field3         | 3000   |
3             |  Table12        |   Field2         | 23123  |
-----------------------------------------------------------

The TableID and TableField fields are PK for me.

The lookup table named Table1 looks like this:
___________________________
Field1   |  Field2  | ... |  Field8  |
--------------------------------------
1000    | 1236     | ... |  2345    |
--------------------------------------

The in comming XML Example is:
<?xml version="1.0" encoding="UTF-8"?>
<root>
      <Record TableID="1" TableField="Field1" Value=""/>
      <Record TableID="2" TableField="Field2" Value=""/>
      <Record TableID="3" TableField="Field3" Value=""/>
</root>

The Returned XML should be like this:
The in comming XML Example is:
<?xml version="1.0" encoding="UTF-8"?>
<root>
      <Record TableID="1" TableField="Field1" Value="1000"/>
      <Record TableID="2" TableField="Field2" Value="2000"/>
      <Record TableID="3" TableField="Field3" Value="3000"/>
</root>

I am just selecting the Value=1000 from Table1 and Field1 and Update the XML.
Please, help me.
I need the exact PL SQL select statement to do this.

This question is important me and very urgent!!!
(500 Points - It wiil be graded as the Best = 500 x4)
Thanks, RRR.
LVL 3
RRRAsked:
Who is Participating?
 
oleggoldConnect With a Mentor Commented:
You can try something like this:

1. test case for Your tables:
 begin
  for i in 1..10
  loop
 insert into meta_tabs
 (tableid, tablename, tablefield, value)
  values
  (i, 'TABLE'||i, 'FIELD'||i,i+i*1000);
  end loop;
  end;

You can use an intermediate table to store Your XML
create table MetaData (col1 xmltype);
then use the following procedure to insert Your XML:

procedure InsXML(xmlDoc IN CLOB, tableName IN VARCHAR2,
PBATCH_SIZE IN PLS_INTEGER DEFAULT 7777)
is    
insCtx DBMS_XMLSave.ctxType;    
rows number;  
begin    
insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
   DBMS_XMLSave.Setbatchsize(insCtx,PBATCH_SIZE);
   DBMS_XMLSave.Setrowtag(insCtx,XMLROWS_TAG);
rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the

document    
dbms_output.put_line(to_char(rows) || ' rows inserted');
writelog(000, to_char(rows) || ' rows inserted');  
DBMS_XMLSave.closeContext(insCtx);            -- this closes the handle
end InsXML;

then the following function will be used from

fetch_xml_val_F  to parse the values and append fields values to :
CREATE OR REPLACE FUNCTION xml_dataF( p_sql         in varchar2,
                           p_max_rows in number )
               RETURN CLOB
                            is
         l_ctx    dbms_xmlquery.ctxType;
         l_xml    clob;
       begin
         dbms_lob.createtemporary( l_xml, true, dbms_lob.session );
         l_ctx := dbms_xmlquery.newContext( p_sql );

         -- setup the context
         dbms_xmlquery.setRaiseNoRowsException( l_ctx,true );
         dbms_xmlquery.setMaxRows( l_ctx, p_max_rows );

         -- get the xml fm the context
         l_xml := dbms_xmlquery.getXML( l_ctx );

         -- print out the xml result set
       --  print_xml( l_xml );

         -- determine the no of rows processed
         dbms_output.put_line( '' );
         dbms_output.put_line( 'No. of rows processed: ' ||
                               dbms_xmlquery.getnumrowsprocessed( l_ctx ) );

         dbms_xmlquery.closeContext(l_ctx);
         return l_xml;
       exception
         when others then
         dbms_xmlquery.closeContext(l_ctx);
       end xml_dataF;
and finaly the getting proc:
create or replace procedure fetch_xml_val_pF
 is
p_xml CLOB;
REC_XML clob;
         l_xml  varchar2(30000);
         l_line varchar2(2000);
         l_amt  pls_integer := 30000;
         l_idx  pls_integer := 1;
       begin
  select t.col1.extract('//root/Record/TableID/text()text()') || ',' ||
  t.col1.extract('//root/Record TableID/text()')
  into  p_xml
 from METADATA t;  
 REC_XML:=xml_dataF(p_sql => 'select value from meta_tabs',p_max_rows => l_idx);
         for i in 1 .. ceil(dbms_lob.getlength(p_xml)/l_amt) loop
           l_xml := dbms_lob.substr(p_xml, l_amt, l_idx);
                     loop
           exit when l_xml is null;
   l_line := substr(l_xml, 1, instr(l_xml, chr(10))-1);
   dbms_output.put_line('|' || l_line);
                       
          l_xml := substr(l_xml, instr(l_xml, chr(10)||REC_XML)+1);
           

           end loop;
         end loop;
       end ;
/
 







the XML output:
ALSO CAN BE USED WITH CLOB PARAMETERS
declare
v_xml clob:='<?xml version="1.0" encoding="UTF-8"?>
<root>
     <Record TableID="1" TableField="Field1" Value=""/>
     <Record TableID="2" TableField="Field2" Value=""/>
     <Record TableID="3" TableField="Field3" Value=""/>
</root>
';
begin
-- select sys.xmldom.(m.col1) into v_xml from metadata m;
  -- Call the procedure
  fetch_xml_val(p_xml => v_xml,
                 ptab=>'META_TABS');
end;

0
 
RRRAuthor Commented:
Some correction:
The Value field in not exists In MetaData table.

Thanks,
RRR.
0
 
LowfatspreadCommented:
you'll probably be better off asking this in the ORACLE database Topic Area,
0
All Courses

From novice to tech pro — start learning today.