XMLDB : DBUriType

HttpUriType The HttpUriType is a subtype of the UriType that provides support for the HTTP protocol. This uses the UTL_HTTP package underneath to access the HTTP URLs. 

The uri begins with the standard http address protocol as http://...... 

Below example creates a table to store and retrive http instances.

create table http_uri_tab ( url httpuritype);

insert into http_uri_tab values 
         (httpuritype.createUri('http://www.oracle.com'));
         
select e.url.getclob() from http_uri_tab e;

DBUriType DBUriType is a subtype of UriType that provides support for DB Uri refs. 

A DB URI ref is an intra-database URL that can be used to reference any row or any row-column data in the database. The URI is an XPATH expression pointing to a table row or column value. 

Thus a schema can be represented as an XML elements which can further contains tables having rows and columns. 

Example

<pre class='language-plsql'><code>
 <?xml version="1.0"?>
     <ORADB>
       <SCOTT>
         <EMPLOYEE>
           <ROWSET>
              <ROW>
               <EMPNO>100</EMPNO>
               <ENAME>John</ENAME>
               <ADDRESS>
                  <STREET>100 Main Street</STREET>
                  <CITY>Jacksonville</CITY>
                  <STATE>FL</STATE>
                  <ZIP>32607</ZIP>
                </ADDRESS>
              </ROW>
              <ROW>
                <EMPNO>200</EMPNO>
                <ENAME>Jack</ENAME>
                <ADDRESS>
                  <STREET>200 Front Street</STREET>
                  <CITY>San Francisco</CITY>
                  <STATE>CA</STATE>
                  <ZIP>94011</ZIP>
                </ADDRESS>
              </ROW>
            </ROWSET>
          </EMPLOYEE>
        </SCOTT>
      </ORADB>
</code></pre>      

In this example, the state to which Employee Jack belongs can be represented with a DBUri as follows

/ORADB/SCOTT/EMPLOYEE/ROW[ENAME="Jack"]/ADDRESS/STATE

You can store these URI types data as values in table columns. The table column types for these URI type is dburitype. 

Below is a simple example to store the Uri for email Id for employee having employee_id = 208 into a table. 

Example

create table emp_dburi_tab (dburl dburitype);

insert into emp_dburi_tab values (
        dburitype.createUri( '/HR/EMPLOYEES/ROW[EMPLOYEE_ID="208"]/EMAIL'));
		
select e.dburl.getclob() from emp_dburi_tab e;		

Output of this select is

<pre class='language-plsql'><code>
<?xml version="1.0"?>
 <EMAIL>WGIETZ2</EMAIL>
</code></pre>

To generate an URI for a particulare table column, you can use the the function sys_dburigen. 

Example - to get the URI for the email of employee with id = 208, you can execute the following select

select sys_dburigen(e.email) AS urlcol
     from hr.employees e
WHERE employee_id = 208;

This will give the following output

SYS.DBURITYPE('/HR/EMPLOYEES/ROW[EMAIL='WGIETZ2']/EMAIL',NULL)

XDBUriType points to an XML document known to the oracle XML DB Repository. 

It provides a way of exposing documents in the Oracle XML DB hierarchy as URIs that can be embedded in URIType columns in tables. 

The URL portion of the XDB URI is the hierarchial address of the targeted repository resource. 

The fragment portion is separated from the URL portion by a '#' sign and is appropriate only if the targetted resource is an XML document, in which case the fragment portion targets one or more parts of the XML document. 

Example

/example/dataset/oraImg.jpg
/example/dataset/xml/employee.xml#/EMPLOYEE_INFO/EMPLOYEE

Here, /example/dataset/xml is a folder resource in the oracle XDB repository. 

oraImg.jpg and employee.xml are resources in /example/dataset. 

/EMPLOYEE_INFO/EMPLOYEE is an xpath expression representing the element EMPLOYEE_INFO and child element EMPLOYEE in employee.xml. 

Below example creates an XDB URI from an xml string, and inserts it into a table having this URIType as a column. 

Below is an XDBUriType example for PO number 999.

DECLARE
res BOOLEAN;
postring   VARCHAR2(100):= '<?xml version="1.0"?>
<ROW>
<PO>999</PO>
</ROW>';
BEGIN
res := DBMS_XDB.CREATEFOLDER('/public/');
res := DBMS_XDB.createFolder('/public/orders/');
res := DBMS_XDB.createResource('/public/orders/po1.xml', postring);
END;
/

Once the uri resource is created, it can be inserted into a table having URIType datatype.

CREATE TABLE uri_tab (poUrl SYS.URIType, poName VARCHAR2(1000));

INSERT INTO uri_tab VALUES
  (URIFACTORY.getURI('/public/orders/po1.xml'), 'SamplePurchaseOrder');

You can then select the column data as follows

SELECT e.poUrl.getCLOB() as poUri FROM uri_tab e where poName = 'SamplePurchaseOrder';

<?xml version="1.0"?>
<ROW>
<PO>999</PO>
</ROW>

With DBUri you can thus access database data with XPATH notation, which lets you visualise the data as XML data. 

The XML string created is of a structure that represents the database structure. 

A DBUri does not reference a global location as does an HTTPUri.