Menu
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
842
There is a setting in the preferences to let it show the actual XML in the Query Results window. Tick 'Display XML Value in Grid', click OK, and re-run your query.
@XmlType allows you to specify a factory class and/or method that can be used to instantiate the domain object instead of the default constructor.
XMLElement takes an element name for identifier , an optional collection of attributes for the element, and arguments that make up the content of the element. It returns an instance of type XMLType .
XMLType is a system-defined opaque type for handling XML data. It as predefined member functions on it to extract XML nodes and fragments. You can create columns of XMLType and insert XML documents into it.
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 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' ) 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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
