Oracle Pl/SQL: Loop through XMLTYPE nodes

Question

Hi, 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 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 2

Hi Pistacchio,

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