4 Replies Latest reply: Mar 21, 2014 9:39 AM by user4423142 RSS

    writing request XQUERY with oracle 11G

    user4423142

      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