Parse xml documents directly from filesystem without loading into Oracle database

First, create a database directory object and grant it read,write privilages.
Create or replace directory xmlload as '/tmp/xmlload';
grant read,write on XMLLOAD to <username>;

-- to avoid some ora-600 errors in 11.2.0.2 version of DB.
alter session set events='31156 trace name context forever, level 0x400';


If a DBA is tasked to read a xml file, he will like to see the output as rows and columns after parsing base on xquery condition...

E.g

WITH vendxml_col AS
(select XMLTYPE(bfilename('XMLLOAD','template.xml'),NLS_CHARSET_ID('AL32UTF8'))vend_doc from dual)
-- Above WITH used to simulate your table/data read from file.
SELECT  a.name,a.operation,a.searchable
  FROM vendxml_col,
       XMLTABLE('for $i in /import_data/template/template_attribute
                                    where $i/@name eq "UPN"
                                    or $i/@searchable eq "n"
                                   return $i'
                PASSING vendxml_col.vend_doc
                COLUMNS
                name varchar2(300) PATH '@name',
operation varchar2(500) PATH '@operation',
searchable varchar2(1) PATH '@searchable') a;

However, if you  want a xml output  as a result of your parsing you can simply read xmltype instead of xmltable.

select xmlquery(
'for $i in /import_data/template/template_attribute
                                    where $i/@name eq "VIJAY"
                                    or $i/@operation_flag=fn:true()
                                   return $i'
PASSING d.xml_doc  returning content) the_result
from
(select xmltype (bfilename('XMLLOAD','template.xml'),NLS_CHARSET_ID('AL32UTF8')) xml_doc from dual) d;

Oracle database link with easy connect, RAC, bequeath connection.

The old way of creating a database link in oracle database is to use the syntax
create database link linkname connect to <username> identified by <password> using 'connect_string';
where connect string maps to a entry in tnsnames.ora.

If you did not want to depend on any files in Oracle local directries, then you had the workaround of  replacing the connect string with the complelete description as follows.
create database  link linkname connect to <username> identified by <password> using
'(DESCRIPTION =   
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.101)(PORT = 1521))   
(CONNECT_DATA =     
(SERVICE_NAME = VIJAY)))';

Now, you can use the same concept and connect using the easy connect syntax as follows.

 create  database link abc1 connect to <username> identified by <password> using '//10.0.10.101:1521/vijay';

If this is a RAC environment and you need to access one specific instance, then the syntax would be 

create  database link abc1 connect to <username> identified by <password> using '//10.0.10.101:1521/vijay/vijay1';


If for some reason, you need to create a database link to read data from the same host (loopback ) you could use a bequeath connection in the database as follows.


create   database link beq_link connect to <username> identified by <password>
USING
  '(description=(address=(protocol=beq)(program=/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle))
    (CONNECT_DATA = (SERVICE = orclprd)))';