SQL & PL/SQL

Hello,

I am having multiple xml files (below is the header of one of the xml file) containing the below header:

<?xml version="1.0" encoding="UTF-8" ?>
<APIBusinessObjects xmlns="http://xmlns.oracle.com/Primavera/P6/V15.1/API/BusinessObjects" 
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                 xsi:schemaLocation="http://xmlns.oracle.com/Primavera/P6/V15.1/API/BusinessObjects 
                                      http://xmlns.oracle.com/Primavera/P6/V15.1/API/p6apibo.xsd">

The xml file contains <ObjectId>, <Id> and <Name> tags under the xml path /APIBusinessObjects/Project.

     /APIBusinessObjects/Project/<ObjectId>

     /APIBusinessObjects/Project/<Id>

     /APIBusinessObjects/Project/<Name>

I am using the following SQL query to retrieve the data within the above tags:

SELECT objectid, id, name
FROM (SELECT XMLTYPE(bfilename('CAN', 'can.xml'), nls_charset_id('UTF-8')) xml_data
  FROM dual)
     , XMLTable('/APIBusinessObjects/Project'
               passing xml_data
               columns objectid number path 'ObjectId',
                       id VARCHAR2(50) path 'Id',
                       name VARCHAR2(100) path 'Name'
    );

After executing the query, no rows are returned.

When I change the xml header to the given below,

<?xml version="1.0" encoding="UTF-8" ?>
<APIBusinessObjects xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

and execute the above query, I get the data in the columns objectid, id, and name.

But I would like to use the xml header given first above which is the default header that I get in the xml files. But due to this type of xml header, the query doesn't any data.

Also I do not want to change the xml header to the second type to get the result using the query.

Could you'll please let me know as to how I can get the result using the first type of xml header and the above query?

Thank you all in advance.

MAKKINO