XMLType

From Oracle FAQ
Jump to: navigation, search

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

Example[edit]

Create table with XMLType column:

create table xml_tab(xml XMLType);

Load data:

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

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,
                       '/DATA/Common/Field/Value[@Name="END_DATE"]/text()',
                       '02.06.2017');
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 #