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
-
1BAD JOB. Either get a blog, or make this community wiki!!!Adriaan Stander– Adriaan Stander2010-10-20 05:36:40 +00:00Commented Oct 20, 2010 at 5:36
-
3How do you make it a "community wiki"? I was under the impression that doing this sort of thing was encouraged, as per the stackoverflow faqcorydoras– corydoras2010-10-20 05:39:05 +00:00Commented Oct 20, 2010 at 5:39
-
It should still be a question, though. "How do I work with" is a bit broad. +1 anyway.Thilo– Thilo2010-10-20 06:08:51 +00:00Commented Oct 20, 2010 at 6:08
-
You probably would not have gotten slammed for it without the statement of intention.Thilo– Thilo2010-10-20 06:10:27 +00:00Commented Oct 20, 2010 at 6:10
-
@astandar please read: meta.stackexchange.com/questions/392/…Sam Saffron– Sam Saffron2010-10-21 05:03:48 +00:00Commented Oct 21, 2010 at 5:03
|
Show 1 more comment
1 Answer
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');