Oracle Pl/SQL: Loop through XMLTYPE nodes

Question

I have a XMLTYPE with the following content:

<?xml version="1.0"?>
    <users>
        <user>
            <name>user1</name>
        </user>
        <user>
            <name>user2</name>
        </user>
        <user>
            <name>user3</name>
        </user>
    </users>

How can I loop in PL/SQL through all the elements "user"? Thanks

Answer 1

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 -------- user1 user2 user3 

Answer 2

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

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

Answer 3

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;