Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

writing request XQUERY with oracle 11G

user4423142Mar 19 2014 — edited Mar 21 2014

Hi,

What is exactly the best way to make an XMLTYPE object (column with type XMLType) request with Oracle 11 g ? (Oracle 11g  Release 11.2.0.3.0)

In different books, we see a lot of different requests.

Simple example :

create table contact_file_tbl(filename varchar2(255), xml xmltype);

insert into contact_file_tbl (filename, xml)

values ('john_smith.xml', xmltype(bfilename('XML_DIR', 'john_smith.xml'),nls_charset_id('AL32UTF8')));

Ok for now

But impossible to make a good query :

select XMLQuery('declare namespace book="http://xmlbook.com/sample/contact.xsd"; (:

for $c in /book:contact

return $c/book:first_name' PASSING a.xml RETURNING CONTENT)

from contact_file_tbl a;

returns nothing.

why this request works ?

SELECT filename, XMLQuery('toto'

         PASSING xml RETURNING CONTENT) dataResult

  FROM contact_file_tbl;

Could you send me a request with this example.

Here the XML document :

<?xml version="1.0" encoding="UTF-8"?>
<contact xmlns="http://xmlbook.com/sample/contact.xsd" id="1">
  
<category>customer</category>
  
<first_name Chinese="约翰">John</first_name>
  
<last_name>Smith</last_name>
  
<email>john.smith@hfarm.com</email>
  
<phone>(512)781-9230</phone>
  
<cellphone>(512)781-9230</cellphone>
  
<address>
      
<street1>1234 sunflower road</street1>
      
<city>austin</city>
      
<state>texas</state>
      
<zipcode>78701</zipcode>
      
<country>USA</country>
  
</address>
  
<references>
    
<reference relationship="account manager, tech sales">
      
<first_name>robert</first_name>
      
<last_name>tian</last_name>
        
<email>robert.tian@xmlbook.com</email>
    
</reference>
    
<reference relationship="account manager, app sales">
      
<first_name>Richard</first_name>
      
<last_name>Liu</last_name>
      
<email>richard.liu@xmlbook.com</email>
    
</reference>
  
</references>
</contact>

Thank you very much

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 18 2014
Added on Mar 19 2014
4 comments
2,175 views