My Oracle Support Banner

How To Extract A Value From Elements In A View (Doc ID 750919.1)

Last updated on APRIL 29, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.
Information in this document applies to any platform.

Goal

Once the rows can be displayed (e.g., as in the view below), how can the values of individual
elements be extracted?

This is the original row:

create table customer_xt
(doc clob);

insert into customer_xt
values('<?xml version="1.0" encoding="windows-1252"?>
<CustomerList>
<Customer>
<NAME>Richard xx</NAME>
<EMAIL>Richard.xx@example.com</EMAIL>
<ADDRESS>Someroad, Somecity, Redwood xx, CA xx, U.S.A</ADDRESS>
<PHONE>xx</PHONE>
<DESCRIPTION>Very Important US Customer</DESCRIPTION>
</Customer>
<Customer>
<NAME>Maira xx</NAME>
<EMAIL>Maira.xx@example.com</EMAIL>
<ADDRESS>Someroad, Somecity, Redwood xx, CA xx, U.S.A</ADDRESS>
<PHONE>xx</PHONE>
<DESCRIPTION>Very Important US Customer</DESCRIPTION>
</Customer>
</CustomerList>');
commit;



This is how the view is created:

create or replace view cust_test_vw
as select extract(value(a), 'Customer/NAME') as NAME,
extract(value(a), 'Customer/EMAIL') as EMAIL,
extract(value(a), 'Customer/ADDRESS') as ADDRESS,
extract(value(a), 'Customer/PHONE') as PHONE,
extract(value(a), 'Customer/DESCRIPTION') as DESCRIPTION
from customer_xt,
table(xmlsequence(extract(xmltype(doc),'/CustomerList/Customer'))) a
/



Selecting from the view we get the values and the elements

set head off
select * from cust_test_vw;
<NAME>Richard xx</NAME> 
<EMAIL>Richard.xx@example.com</EMAIL> 
<ADDRESS>Someroad, Somecity, Redwood xx, CA xx, U.S.A</ADDRESS> 
<PHONE>xx</PHONE> 
<DESCRIPTION>Very Important US Customer</DESCRIPTION>
 

<NAME>Maira xx</NAME> 
<EMAIL>Maira.xx@example.com</EMAIL> 
<ADDRESS>Someroad, Somecity, Redwood xx, CA xx, U.S.A</ADDRESS> 
<PHONE>xx</PHONE> 
<DESCRIPTION>Very Important US Customer</DESCRIPTION>
 


2 rows selected. 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.