XMLType

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

Example

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	#