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 ?

Answer 1

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;