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