Quick introduction on how to use oracle xml data type

Question

How do you work with the oracle XML data type?

This question is intended to be asked and answered by me, just to share the information with others

Answer 1

The following sample SQL demonstrates how to insert, query and update database fields that contain the XMLTYPE data type:

-- Create a table that can store XML
create table sample_xml (id number, xml xmltype);

-- Insert some XML into the table
insert into sample_xml values (1, xmltype.createxml('<subject><name>test</name><list><li>a</li><li>b</li></list></subject>'));
insert into sample_xml values (2, xmltype.createxml('<subject><name>test</name><list><li>a</li></list></subject>'));

-- When doing a select, refer to table using the alias or getClobVal() will not work
select t.id, t.xml.getClobVal() from sample_xml t;

-- Update text of a single xml element
UPDATE sample_xml SET xml = UPDATEXML(xml, '/subject/name/text()','happy') WHERE id = 2;

-- Select out just the name for each row in the table using xpath
select id, extractvalue(xml, '/subject/name/text()') from sample_xml;

-- Doing an sample_xml, where the xpath string matches two elements, the text of both elements will be updated
UPDATE sample_xml SET xml = UPDATEXML(xml, '/subject/list/li/text()','one');