oracle plsql: how to parse XML and insert into table

Question

How to load a nested xml file into database table ?

<?xml version="1.0" ?> 
<person>
   <row>
       <name>Tom</name>
       <Address>
           <State>California</State>
           <City>Los angeles</City>
       </Address>
   </row>
   <row>
       <name>Jim</name>
       <Address>
           <State>California</State>
           <City>Los angeles</City>
       </Address>
   </row>
</person>

In this xml, person is the table name , name is the filed name, Tom is its filed value. Address is a subtable and state and city is two column inside Address. I want to insert the person row into person table, if it failed , do not insert into address table. This xml could be very big. What's the best solution to do this ?

Try this https://forums.oracle.com/forums/thread.jspa?messageID=10269899 — Jacob, Oct 20 '12 at 04:03
i got little different xml tree, pls help :) http://stackoverflow.com/questions/18583872/oracle-xml-skip-not-exist-node — zrosystem, Sep 03 '13 at 05:00

score 33 · Accepted Answer · answered Oct 22 '12 at 03:24

33

You can load an XML document into an XMLType, then query it, e.g.:

DECLARE
  x XMLType := XMLType(
    '<?xml version="1.0" ?> 
<person>
   <row>
       <name>Tom</name>
       <Address>
           <State>California</State>
           <City>Los angeles</City>
       </Address>
   </row>
   <row>
       <name>Jim</name>
       <Address>
           <State>California</State>
           <City>Los angeles</City>
       </Address>
   </row>
</person>');
BEGIN
  FOR r IN (
    SELECT ExtractValue(Value(p),'/row/name/text()') as name
          ,ExtractValue(Value(p),'/row/Address/State/text()') as state
          ,ExtractValue(Value(p),'/row/Address/City/text()') as city
    FROM   TABLE(XMLSequence(Extract(x,'/person/row'))) p
    ) LOOP
    -- do whatever you want with r.name, r.state, r.city
  END LOOP;
END;

answered Oct 22 '12 at 03:24

Jeffrey Kemp

59,135
14
106
158

This may help, but one big issue is that I may not know the tag name, the input is dynamic xml with all different table names and fileds – Frank Oct 22 '12 at 17:33
4

Well, you need to know what the tags are, otherwise how is the code supposed to know where to insert the data? – Jeffrey Kemp Oct 24 '12 at 08:47
I have solved this problem by using dbms_xmldom API. Thank you for your help – Frank Oct 24 '12 at 19:10

score 17 · Answer 2 · answered May 23 '14 at 13:53

select *  
FROM XMLTABLE('/person/row'  
         PASSING   
            xmltype('
                <person>
                   <row>
                       <name>Tom</name>
                       <Address>
                           <State>California</State>
                           <City>Los angeles</City>
                       </Address>
                   </row>
                   <row>
                       <name>Jim</name>
                       <Address>
                           <State>California</State>
                           <City>Los angeles</City>
                       </Address>
                   </row>
                </person>
            ')
         COLUMNS  
            --describe columns and path to them:  
            name  varchar2(20)    PATH './name',  
            state varchar2(20)    PATH './Address/State',  
            city  varchar2(20)    PATH './Address/City'
     ) xmlt  
;

When I have multiple Address tags inside the it is getting failed. What should I modify to loop through multiple tags with same name? Please help — Murali, Mar 16 '16 at 18:57
If you want to access the first one, then xpath will be like: './Address[1]/City' — daggett, Mar 19 '16 at 06:00

score 6 · Answer 3 · edited Aug 07 '13 at 13:04

CREATE OR REPLACE PROCEDURE ADDEMP
    (xml IN CLOB)
AS
BEGIN
    INSERT INTO EMPLOYEE (EMPID,EMPNAME,EMPDETAIL,CREATEDBY,CREATED)
    SELECT 
        ExtractValue(column_value,'/ROOT/EMPID') AS EMPID
       ,ExtractValue(column_value,'/ROOT/EMPNAME') AS EMPNAME
       ,ExtractValue(column_value,'/ROOT/EMPDETAIL') AS EMPDETAIL
       ,ExtractValue(column_value,'/ROOT/CREATEDBY') AS CREATEDBY
       ,ExtractValue(column_value,'/ROOT/CREATEDDATE') AS CREATEDDATE
    FROM   TABLE(XMLSequence( XMLType(xml))) XMLDUMMAY;

    COMMIT;
END;

oracle plsql: how to parse XML and insert into table

3 Answers3

Linked