Parse xml in stored procedure oracle

Question

I have a List which has a list type property (Address) inside it. I am sending the whole information as an xml in database to update/insert the details in my oracle table.

Here is the xml format

     DECLARE
  x XMLType := XMLType( '

    <person>   
    <row>       
    <name>Tom</name>       
    <Address>          
    <LocalAddress>           
    <State>California</State>           
    <City>Los angeles</City>          
    </LocalAddress>          
    <LocalAddress>           
    <State>California1</State>           
    <City>Los angeles1</City>          
    </LocalAddress>       
    </Address>   
    </row>   
    <row>       
    <name>Jim</name>       
    <Address>    
    <LocalAddress> 
    <State>California</State>           
    <City>Los angeles</City>      
    </LocalAddress> 
    </Address>   
    </row>
    </person>');
    v_person_name varchar2(1000);
    v_city varchar2(1000);
    BEGIN
      FOR xmlrow IN
      (SELECT column_value AS xml
      FROM TABLE(XMLSequence(x.extract('/person/row')))
      )
    LOOP
      SELECT extractValue(xmlrow,'/row/name/text()')
      INTO v_person_name
      FROM dual;
      DBMS_OUTPUT.put_line(v_person_name);
      FOR address IN
      (SELECT column_value AS xml
      FROM TABLE(xmlsequence(xmlrow.extract('/row/Address/LocalAddress')))
      )
      LOOP
        -- extract address values same as above.
        SELECT extractValue(xmlrow,'/LocalAddress/City/text()')
        INTO v_city
        FROM dual;
        DBMS_OUTPUT.put_line(v_city);
      END LOOP;
    END LOOP;
    END;

How can I parse this whole xml in oracle? I am sending this as xmltype variable.

It is giving me a comilation error

Error report -
ORA-06550: line 35, column 27:
PLS-00382: expression is of wrong type
ORA-06550: line 35, column 14:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
ORA-06550: line 35, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 41, column 37:
PLS-00302: component 'EXTRACT' must be declared
ORA-06550: line 41, column 37:
PLS-00302: component 'EXTRACT' must be declared
ORA-06550: line 41, column 30:
PL/SQL: ORA-00904: "XMLROW"."EXTRACT": invalid identifier
ORA-06550: line 40, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 45, column 29:
PLS-00382: expression is of wrong type
ORA-06550: line 45, column 16:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
ORA-06550: line 45, column 9:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

Please help me to resolve this. Thanks in advance.

user1685652 · Accepted Answer · 2014-06-19 06:47:32Z

I am able to parse the nested xml using this method

SET serveroutput ON format wrapped;
DECLARE
  x XMLType := XMLType(' 
<person>   
<row>       
<name>Tom</name>       
<Address>          
<LocalAddress>           
<State>California</State>           
<City>Los angeles</City>          
</LocalAddress>          
<LocalAddress>           
<State>California1</State>           
<City>Los angeles1</City>          
</LocalAddress>       
</Address>   
</row>   
<row>       
<name>Jim</name>       
<Address>    
<LocalAddress> 
<State>California</State>           
<City>Los angeles</City>      
</LocalAddress> 
</Address>   
</row>
</person>');
BEGIN
  FOR r IN
  (SELECT ExtractValue(Value(p),'/row/name/text()') AS name ,
    Extract(Value(p),'/row/Address') As Address
  FROM TABLE(XMLSequence(Extract(x,'/person/row'))) p
  )
  LOOP
    DBMS_OUTPUT.put_line(r.name);
    FOR row1 IN
    (SELECT ExtractValue(Value(l),'/LocalAddress/City/text()') AS city 
    FROM TABLE(XMLSequence(Extract(r.Address,'/Address/LocalAddress'))) l
    )
    LOOP

      DBMS_OUTPUT.put_line(row1.city);
      -- do whatever you want with r.name, r.state, r.city
    END LOOP;
    -- do whatever you want with r.name, r.state, r.city
  END LOOP;
END;

Nick · Accepted Answer · 2014-06-19 13:08:53Z

0

As you are using the XML type, I'm assuming you need to access individual tags and their respective values. Below is a sample bit of code that would select the names from each row given your XML structure above. Note: xml_in is just a variable name for the xmltype variable in the stored procedure.

select extractValue(column_value,'/row/name/text()') as name
from table (XMLSequence(xml_in.extract('/person/row')));

If you need to do nested parsing, you can also do:

for xmlrow in (select column_value as xml from table(xmlsequence(xml_in.extract('/person/row'))))) loop

   select extractValue(xmlrow.xml,'/row/name/text()')
    into v_person_name
     from dual;

    for address in (select column_value as xml from table(xmlsequence(xmlrow.xml.extract('/row/Address/LocalAddress')))) loop
         -- extract address values same as above.
     end loop;
end loop;

edited Jun 19, 2014 at 13:08

answered Jun 18, 2014 at 12:56

Nick

2,51419 silver badges26 bronze badges

5 Comments

user1685652 Over a year ago

Thanks, but how to extract the address part.

Nick Over a year ago

Just use XPath. So extractValue(column_value,'/row/Address/LocalAddress/State/text()') and so on.

user1685652 Over a year ago

Its a dynamic xml, I didn't know how many local address I can get inside address.

user1685652 Over a year ago

I have edited my code, I tried the option that you suggested but it is giving me a compilation error.

Nick Over a year ago

Updated my code. You have to specify cursor.column_name, so in my example, it would need to be xmlrow.xml.

Collectives™ on Stack Overflow

Parse xml in stored procedure oracle

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related