I am now studying oracle xml db.I want to generate a xml document from the xml schema-based table,but the result is ORA-00904
firstly
I registered the xml schema
SQL> declare
2 doc varchar2(10000) := '<schema
3 targetNamespace="http://localhost/test.xsd"
4 xmlns:cis="http://localhost/test.xsd"
5 xmlns:xdb="http://xmlns.oracle.com/xdb"
6 xmlns="http://www.w3.org/2001/XMLSchema">
7 <complexType name="ResearchType">
8 <sequence>
9 <element name="PatientID" type="string" xdb:SQLName="PatientID" xdb:SQLType="VARCHAR2"/>
10 <element name="Diagnose" type="string" xdb:SQLName="Diagnose" xdb:SQLType="VARCHAR2"/>
11 </sequence>
12 </complexType>
13 <element name="Research" type="cis:ResearchType"/>
14 </schema>';
15 begin
16 dbms_xmlschema.registerSchema('http://localhost/test.xsd', doc);
17 end;
18 /
PL/SQL procedure successfully completed
Secondly
I created the table test_data based on the xml schema
SQL> CREATE TABLE test_data OF XMLTYPE
2 XMLSCHEMA "http://localhost/test.xsd" ELEMENT "Research";
Table created
Thirdly
I inserted one row into the table test_data
SQL> insert into test_data values (xmltype('<Research
2 xmlns="http://localhost/test.xsd"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 xsi:schemaLocation="http://localhost/test.xsd
5 http://localhost/test.xsd">
6 <PatientID>ZY1001</PatientID>
7 <Diagnose>disease</Diagnose>
8 </Research>'));
1 row inserted
SQL> commit;
Commit complete
Forthly
I can select the data form the table.
SQL> select t.XMLData."PatientID" ,
2 t.XMLData."Diagnose"
3 from test_data t
4 ;
XMLDATA.PatientID XMLDATA.Diagnose
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
ZY1001 disease
But Finally when i used the xmlelement() function to generate the xml document,
the database returnd the error"ORA-00904"
SQL> SELECT XMLELEMENT ( "Diagnose" ,e.PatientID) AS "result"
2 FROM test_data e
3 ;
SELECT XMLELEMENT ( "Diagnose" ,e.PatientID) AS "result"
FROM test_data e
ORA-00904: "E"."PATIENTID": invalid identifier
WHY????