Oracle Pl/SQL: Loop through XMLTYPE nodes

You can loop through the elements using EXTRACT and XMLSequence (splits the XML into distinct chunks -- here users) like this:

SQL> SELECT extractvalue(column_value, '/user/name') "user"  2    FROM TABLE(XMLSequence(XMLTYPE(  3                 '<?xml version="1.0"?>  4                     <users>  5                         <user>  6                             <name>user1</name>  7                         </user>  8                         <user>  9                             <name>user2</name> 10                         </user> 11                         <user> 12                             <name>user3</name> 13                         </user> 14                     </users>').extract('/users/user'))) t;user--------user1user2user3

You can use XQuery. Check out the select statement below. v_xml_doc is the XMLTYPE variable containing the XML data.

select namefrom   XMLTable('for $i in /users/user                            return $i'                            passing   v_xml_doc                            columns   name varchar2(200) path 'name'               )

How about this:

PROCEDURE xmltest IS  v_userlist XMLType;  v_count NUMBER(38) := 1;BEGIN  /* define XML variable */  v_userlist := XMLType('<?xml version="1.0"?>    <users>        <user>            <name>user1</name>        </user>        <user>            <name>user2</name>        </user>        <user>            <name>user3</name>        </user>    </users>');  /* for each user, print out their name (each element can be extracted using xpath '//user[1]' '//user[2]' etc) */  WHILE v_userlist.existsNode('//user[' || v_count || ']') = 1 LOOP    dbms_output.put_line(v_userlist.extract('//user[' || v_count || ']/name/text()').getStringVal());    v_count := v_count + 1;  END LOOP;END;