Database Software

I'm trying to write a simple select statement to return the entire content of an XML document, and I keep getting errors. I've been reading and rereading the online documentation, the Scardina/Chang/Wang book, and the FAQ's here; and I just don't get it.

My environment is:
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Dec 26 10:47:38 2006

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

(Unless I install the latest and greatest version of Oracle on my workstation, I can't upgrade the version of Oracle I'm running).

Here's the beginning of my schema: <?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" id="StetonAuditResults" xdb:storeVarrayAsTable="true">
<xs:element name="StetonAuditResults" xdb:defaultTable="STETON_AUDIT_RESULTS">
<xs:complexType xdb:SQLType="STETON_AUDIT_INFO_T">
<xs:sequence>


I've registered the schema using
begin
dbms_xmlschema.registerSchema
(schemaURL => 'http://xmlns.oracle.com/xdb/Steton.xsd',
schemaDoc => xdbURIType('/datamart/CustomerCare/xsd/Steton.xsd').getClob(),
genTables => TRUE,
local => TRUE,
genBean => FALSE,
genTypes => TRUE
) ;
end;
/

Using Windows Explorer I've dragged and dropped two files into /datamart/CustomerCare/xml directory. They have a size 0, so they've been "shredded".

Here's the commands that I've tried to run.

describe steton_audit_results;
select count(*) from STETON_AUDIT_RESULTS;
select STETON_AUDIT_INFO_T from STETON_AUDIT_RESULTS;
select STETON_AUDIT_RESULTS from STETON_AUDIT_RESULTS;
select STETONAUDITRESULTS from STETON_AUDIT_RESULTS;
select StetonAuditResults from STETON_AUDIT_RESULTS;
select "StetonAuditResults" from STETON_AUDIT_RESULTS;
select OBJECT_VALUE from STETON_AUDIT_RESULTS;
select EXTRACTVALUE('OBJECT_VALUE', '/StetonAuditResults') from STETON_AUDIT_RESULTS;
select EXTRACT('OBJECT_VALUE', '/StetonAuditResults') from STETON_AUDIT_RESULTS;

and here's the responses:

SQL> describe steton_audit_results;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://xmlns.oracle.com/xdb/Steton.xsd" Element "StetonAuditResults"

SQL> select count(*) from STETON_AUDIT_RESULTS;

COUNT(*)
----------
2

SQL> select STETON_AUDIT_INFO_T from STETON_AUDIT_RESULTS;
select STETON_AUDIT_INFO_T from STETON_AUDIT_RESULTS
*
ERROR at line 1:
ORA-00904: "STETON_AUDIT_INFO_T": invalid identifier


SQL> select STETON_AUDIT_RESULTS from STETON_AUDIT_RESULTS;
select STETON_AUDIT_RESULTS from STETON_AUDIT_RESULTS
*
ERROR at line 1:
ORA-00904: "STETON_AUDIT_RESULTS": invalid identifier


SQL> select STETONAUDITRESULTS from STETON_AUDIT_RESULTS;
select STETONAUDITRESULTS from STETON_AUDIT_RESULTS
*
ERROR at line 1:
ORA-00904: "STETONAUDITRESULTS": invalid identifier


SQL> select StetonAuditResults from STETON_AUDIT_RESULTS;
select StetonAuditResults from STETON_AUDIT_RESULTS
*
ERROR at line 1:
ORA-00904: "STETONAUDITRESULTS": invalid identifier


SQL> select "StetonAuditResults" from STETON_AUDIT_RESULTS;
select "StetonAuditResults" from STETON_AUDIT_RESULTS
*
ERROR at line 1:
ORA-00904: "StetonAuditResults": invalid identifier


SQL> select OBJECT_VALUE from STETON_AUDIT_RESULTS;
ERROR:
ORA-21500: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s],
[%s], [%s]



SQL> select EXTRACTVALUE('OBJECT_VALUE', '/StetonAuditResults') from STETON_AUDIT_RESULTS;
select EXTRACTVALUE('OBJECT_VALUE', '/StetonAuditResults') from STETON_AUDIT_RESULTS
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -


SQL> select EXTRACT('OBJECT_VALUE', '/StetonAuditResults') from STETON_AUDIT_RESULTS;
select EXTRACT('OBJECT_VALUE', '/StetonAuditResults') from STETON_AUDIT_RESULTS
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -


SQL>

So what is the SQL select statement that would return the contents of one or both of the files that have been stored in the XML DB repository. I've been banging my head on this project for three weeks and I just don't get it yet.

Message was edited by:
user547369