Problem with XML

From: <fordesperado_at_yahoo.com>
Date: 24 May 2006 07:22:21 -0700
Message-ID: <1148480541.151257.299670@i39g2000cwa.googlegroups.com>

Hi all,

I cannot retrieve XML inserted into XMLType.

select id, p.po.extract('//PONUM/text()').getNUMBERval() p, p.po.extract('//COMPANY/text()').getstringval() p1 from po p

will only show me the IDs but never XML nodes. Here is the full script:

declare

        doc varchar2(1000) := '<schema
targetNamespace="http://www.oracle.com/ABCD.xsd" xmlns:po="http://www.oracle.com/ABCD.xsd" xmlns="http://www.w3.org/2001/XMLSchema">  <complexType name="PURORDTYPE">
  <sequence>
   <element name="PONUM" type="decimal"/>    <element name="COMPANY">
    <simpleType>
<restriction base="string">

      <maxLength value="15"/>


     </restriction>

    </simpleType>
   </element>
  </sequence>
 </complexType>
 <element name="PURORD" type="po:PURORDTYPE"/> </schema>';
begin
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://www.oracle.com/ABCD.xsd',     SCHEMADOC => doc,
    LOCAL => TRUE,
    GENTYPES => TRUE,
    GENTABLES => TRUE);
end;

create table po(
 id number,
 po sys.XMLType not null
)
  xmltype column po
    XMLSCHEMA "http://www.oracle.com/ABCD.xsd"     element "PURORD";

descr po;

insert into po values (1,
  xmltype('<po:PURORD xmlns:po="http://www.oracle.com/ABCD.xsd"    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xsi:schemaLocation="http://www.oracle.com/ABCD.xsd   http://www.oracle.com/ABCD.xsd">

<PONUM>1001</PONUM>

       <COMPANY>ORACLE</COMPANY>
 </po:PURORD>'));
insert into po values (2,
  xmltype('<po:PURORD xmlns:po="http://www.oracle.com/ABCD.xsd"    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xsi:schemaLocation="http://www.oracle.com/ABCD.xsd   http://www.oracle.com/ABCD.xsd">

<PONUM>1002</PONUM>

       <COMPANY>IBM</COMPANY>
 </po:PURORD>'));

select id, p.po.extract('//PONUM/text()').getNUMBERval() p, p.po.extract('//COMPANY/text()').getstringval() p1 from po p

BEGIN
dbms_xmlschema.deleteSchema('http://www.oracle.com/ABCD.xsd',

            dbms_xmlschema.DELETE_CASCADE_FORCE); END; TIA
Ford Received on Wed May 24 2006 - 09:22:21 CDT