Procedure to load XML / Excel data into table

create or replace procedure proc_parseXml(pRawXMLdata clob)  is
   v_parser        Dbms_Xmlparser.Parser;
   v_Xml_Clob      Clob;
   v_Doc           Dbms_Xmldom.Domdocument;

   v_Child_Node    dbms_xmldom.domnodelist;
   v_xml_Node      Dbms_Xmldom.Domnode;


   v_empno         number;
   v_ename         varchar2(20);
   v_job           varchar2(20);
   v_hiredate      varchar2(20);

Begin
      -- CLOB data

  v_Xml_Clob :=pRawXMLdata ;
   -- Create XML Parser.
   v_parser  := Dbms_Xmlparser.Newparser;
   -- Parse XML into DOM object                     
   Dbms_Xmlparser.Parseclob(v_parser , v_Xml_Clob);
   -- XML Document               
   v_Doc := Dbms_Xmlparser.Getdocument(v_parser );
   -- Root element
    v_Child_Node:=dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(v_doc),'/employees/employee');
   --
   For I in 0..dbms_xmldom.getlength(v_Child_Node)-1 LOOP
        v_xml_Node := dbms_xmldom.item(v_Child_Node,I);
       
        DBMS_XSLPROCESSOR.valueof(v_xml_Node,'empno/text()',v_empno);
        DBMS_XSLPROCESSOR.valueof(v_xml_Node,'ename/text()',v_ename);
        DBMS_XSLPROCESSOR.valueof(v_xml_Node,'job/text()',v_job);
        DBMS_XSLPROCESSOR.valueof(v_xml_Node,'hiredate/text()',v_hiredate);
       
        insert into emp(empno, ename, job, hiredate) values(v_empno,v_ename,v_job,v_hiredate);
   End LOOP;   
 

 
End;

/


--Call procedure proc_parseXml


declare
v_rawXMLData clob;
begin
v_rawXMLData :=  '<employees> <employee> <empno>7369</empno><ename>SMITH</ename> <job>CLERK</job> <hiredate>17-DEC-1980</hiredate> </employee>
<employee><empno>7499</empno><ename>ALLEN</ename><job>SALESMAN</job><hiredate>20-FEB-1981</hiredate></employee></employees>';

proc_parseXml(v_rawXMLData );
end;

/




Comments

Post a Comment

Popular posts from this blog

Table high water mark tips

  Question:  Can you please explain the concept of a table high water mark (HWM)?  I understand that the data is always below the high water mark.  Can you show a script to display the high water mark for a table? Answer:   The high water mark (HWM) for an Oracle table is a construct that shows the table at its greatest size.  Just as a lake has a high-water mark after a draught, an Oracle table has a high water mark that shows the greatest size of the table, the point at which it consumed the most extents. Also see this great script to  display all high water marks . As a table undergoes deletes and updates, rows shrink and table data blocks become empty.  For performance reasons, Oracle keeps the high water mark for a table rather than re-calculate the high water mark after blocks at the "end" of the table (the last extent) becomes empty. For example assume that you have a million row table that takes 30 seconds to read.  After deleting 900...
Read more

Create table of two consecutive months if date range belong to two months

select to_date('01-JUL-2019')+level-1 "JULY", CASE WHEN MONTHS_BETWEEN('09-AUG-2019','01-JUL-19')>1 THEN CASE WHEN LEVEL<=TO_NUMBER(TO_CHAR(TO_DATE('09-AUG-2019'),'DD')) THEN ADD_MONTHS('01-JUL-2019',1)+LEVEL-1 END END "August" FROM DUAL CONNECT BY LEVEL<=CASE WHEN MONTHS_BETWEEN('09-AUG-2019', '01-JUL-19')>1 THEN TO_NUMBER(TO_CHAR(LAST_DAY('01-JUL-2019'),'DD')) ELSE TO_DATE('09-AUG-2019')-TO_DATE('01-JUL-2019') +1 END ----------------------------------- ANOTHER METHOD ----------------------------------- SELECT "JULY", "AUGUST" FROM ( SELECT ROWNUM RN, CASE WHEN TO_CHAR(TO_DATE(MNTH),'MON')='JUL' THEN MONTH END "JULY" FROM ( SELECT TO_DATE('01-JUL-2019')+LEVEL-1 MNTH FROM DUAL CONNECT BY LEVEL<=TO_DATE('09-AUG-19')-T0_DATE('01-JUL-2019')+1 ...
Read more

Fill null values with last non-null amount - Oracle SQL

  I have a table that has 4 columns: Item, Year, Month, Amount. Some of the values for Amount are null and when that happens I want to fill those values in with the previous Amount value that is not null. I can easily do this with the LAG function when there is only one null value but when there are multiple in a row I am not sure how to approach it. Below is an example of what the table might look like with an added column for what I want to add in my query: Item | Year | Month | Amount | New_Amount AAA | 2013 | 01 | 100 | 100 AAA | 2013 | 02 | | 100 AAA | 2013 | 03 | 150 | 150 AAA | 2013 | 04 | 125 | 125 AAA | 2013 | 05 | | 125 AAA | 2013 | 06 | | 125 AAA | 2013 | 07 | | 125 AAA | 2013 | 08 | 175 | 175 I had two ideas which I can't seem to get to work to produce what I want. First I was going to use LAG but then I noticed when there are multiple null values in a row it won't satisfy that. Next I was going...
Read more