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 forums.oracle.com/forums/thread.jspa?messageID=10269899 — Jacob
– Jacob, Commented Oct 20, 2012 at 4:03
i got little different xml tree, pls help :) stackoverflow.com/questions/18583872/… — zrosystem
– zrosystem, Commented Sep 3, 2013 at 5:00

Jeffrey Kemp · Accepted Answer · 2012-10-22 03:24:46Z

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, 2012 at 3:24

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Frank Over a year ago

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

Jeffrey Kemp Over a year ago

Well, you need to know what the tags are, otherwise how is the code supposed to know where to insert the data?

Frank Over a year ago

I have solved this problem by using dbms_xmldom API. Thank you for your help

daggett · Accepted Answer · 2014-05-23 13:53:04Z

17

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  
;

answered May 23, 2014 at 13:53

daggett

29k3 gold badges52 silver badges61 bronze badges

2 Comments

Murali Over a year ago

When I have multiple Address tags inside the <person><row> it is getting failed. What should I modify to loop through multiple tags with same name? Please help

daggett Over a year ago

If you want to access the first one, then xpath will be like: './Address[1]/City'

qujck · Accepted Answer · 2013-08-07 13:04:43Z

6

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;

edited Aug 7, 2013 at 13:04

qujck

14.6k4 gold badges48 silver badges77 bronze badges

answered Aug 7, 2013 at 12:38

Hemant Kumar Bankey

692 silver badges3 bronze badges

Collectives™ on Stack Overflow

oracle plsql: how to parse XML and insert into table

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related