Oracle Database 11g Building Oracle XML DB Applications

Storing XML in XML DB Repository

To load the contact documents into the Oracle database, we can use Oracle XML DB Repository’s FTP protocol access.

With the XML documents stored in XML DB Repository, we manage the XML data using the Oracle XML DB functions.

Creating an SQL View

Using the XPath content analysis result, we can create an SQL view of the contacts using XMLTable() (create_contact_vw.sql):

Listing 17-2 Creating Contact SQL View with XMLTable()

create or replace view contact_vw as

select c.*, rv.any_path from resource_view rv,

XMLTable(XMLNamespaces(DEFAULT ‘http://xmlbook.com/sample/contact.xsd’,

‘http://xmlns.oracle.com/xdb/XDBResource.xsd’ as “ns”),

‘$r/ns:Resource/ns:Contents/*’ passing rv.res as “r”

COLUMNS

id number PATH ‘/contact/@id’, ...