From Oracle FAQ
Jump to: navigation, search

XMLType is an Oracle datatype that can be used to store XML documents in an Oracle database.


Create table with XMLType column:

create table xml_tab(xml XMLType);

Load data:

insert into xml_tab values ('
  <Label>Begin Date </Label>
  <Value Name="BEG_DATE">02.05.2006</Value>
  <Label>End Date</Label>
  <Value Name="END_DATE">01.06.2016</Value>
  <Value Name="TYPE">Example</Value>
  <Value Name="CUST_NAME">Smith</Value>
  <Label>Date of birth</Label>
  <Value Name="CUST_BIRTH_DATE">27.08.1972</Value>

Extract data:

select extract(xml, '/DATA/Customer/Field/Label') from XML_TAB;
select extractValue(xml, '/DATA/Customer/Field/Value[@Name="CUST_BIRTH_DATE"]')
from XML_TAB;

Update XML column:

UPDATE xml_taB
   SET xml = updateXML(xml,
Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #