wxII
asked on
why ORA-00904: "OBJECT_VALUE": invalid identifier
hi,
i know this might be plain question, but i tried to run some examples using XMLQuery & XMLTable from Oracle documentation, it return me with this ORA-00904: "OBJECT_VALUE": invalid identifier error.
i know this might be plain question, but i tried to run some examples using XMLQuery & XMLTable from Oracle documentation, it return me with this ORA-00904: "OBJECT_VALUE": invalid identifier error.
what was the example code you were running?
ASKER
hi there,
the example was
SELECT XMLQuery('for $i in /PurchaseOrder
where $i/CostCenter eq "A10"
and $i/User eq "SMCCAIN"
return <A10po pono="{$i/Reference}"/>'
PASSING OBJECT_VALUE
RETURNING CONTENT)
FROM purchaseorder;
in "http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb_xquery.htm#CBAIFJJJ"
"Example 18-10 Using XMLQuery with Schema-Based Data"
thanks
the example was
SELECT XMLQuery('for $i in /PurchaseOrder
where $i/CostCenter eq "A10"
and $i/User eq "SMCCAIN"
return <A10po pono="{$i/Reference}"/>'
PASSING OBJECT_VALUE
RETURNING CONTENT)
FROM purchaseorder;
in "http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb_xquery.htm#CBAIFJJJ"
"Example 18-10 Using XMLQuery with Schema-Based Data"
thanks
For that example to work, dbmullen stated above, XML DB must be installed.
select * from dba_registry where comp_name like '%XML%'
if that query doesn't return the "Oracle XML Database" component,
you need to request your DBA to install it.
select * from dba_registry where comp_name like '%XML%'
if that query doesn't return the "Oracle XML Database" component,
you need to request your DBA to install it.
this isn't granted to public
select * from dba_registry
you'll need someone with DBA privs to run the query and install XDB
select * from dba_registry
you'll need someone with DBA privs to run the query and install XDB
ASKER
hi there,
it returned me value COMP_ID is "XDB",COMP_NAME is "Oracle XML Database" and the status us "VALID"
it returned me value COMP_ID is "XDB",COMP_NAME is "Oracle XML Database" and the status us "VALID"
Are you querying the purchaseorder table of the OE schema?
ASKER
oh, i'm sorry, i queried on HR instead on OE schema, my mistakes.. . the example works alright in OE; but why others don't have OBJECT_VALUE or COLUMN_VALUE pseudocolumn available?? what do i need to do in order to have these feature. most of the example for xml in xmltable and xmlquery has lengthy usage on those pseudocolumns. any way to enable or check it?
please advise,
WXII
please advise,
WXII
your HR schema (or some synonym you can reach) must have a table called purchaseorder that is not an xmltype table.
The OE.purchaseorder table is an xmltype table, that is it has no columns, the table is itself an xml document, so querying it you only have object_value for the contents of it.
The OE.purchaseorder table is an xmltype table, that is it has no columns, the table is itself an xml document, so querying it you only have object_value for the contents of it.
ASKER
that means xmltype column can't access object_value? so everytime i need to get data, i have to explicitly specify the column name instead? the example actually show something that i need, to be able to transform the xml data into relational row and columns. other than the object_value techniques, how am i going to return the data in relational format with xmltype column, the data i need were all reside within attributes of each nested elements?
The oe.purchaseorder example table is
create table oe.purchaseorder of xmltype;
which is handled differently than
create table my_purchase_order (xml xmltype);
since the oe.purchaseorder table is of xmltype, the "rows" in it are referred to by their object handle which is "object_value"
if you have an xmltype column then you use that
SELECT XMLQuery('for $i in /PurchaseOrder
where $i/CostCenter eq "A10"
and $i/User eq "SMCCAIN"
return <A10po pono="{$i/Reference}"/>'
PASSING xml
RETURNING CONTENT)
FROM my_purchase_order;
create table oe.purchaseorder of xmltype;
which is handled differently than
create table my_purchase_order (xml xmltype);
since the oe.purchaseorder table is of xmltype, the "rows" in it are referred to by their object handle which is "object_value"
if you have an xmltype column then you use that
SELECT XMLQuery('for $i in /PurchaseOrder
where $i/CostCenter eq "A10"
and $i/User eq "SMCCAIN"
return <A10po pono="{$i/Reference}"/>'
PASSING xml
RETURNING CONTENT)
FROM my_purchase_order;
ASKER
on the PASSING and RETURN keyword, can i return in row format? as like relational?
Sorry, I don't know what you're asking.
Can you illustrate using a table with an xmltype column and the sample data you used in one of your previous questions?
Can you illustrate using a table with an xmltype column and the sample data you used in one of your previous questions?
ASKER
hi there,
sorry for late reply, the example table consist the xmltype column call nmstruct reside inside bststruct table, all the xml data will
reside in nmstruct column.
the data is as follow:-
<country>
<id>MAL</id>
<sponsornode spacctid="100000" spacctcode="100000" level="0" legno="1">
<sponsornode spacctid="NSH" spacctcode="nscode" level="1" legno="1"/>
<sponsornode spacctid="WXII" spacctcode="wxcode" level="1" legno="2"/>
<sponsornode spacctid="JOH" spacctcode="jhcode" level="1" legno="3"/>
<sponsornode spacctid="SNK" spacctcode="sncode" level="1" legno="4"/>
<sponsornode spacctid="KEN" spacctcode="nnnn" level="1" legno="5"/>
</sponsornode>
</country>
with XMLQuery or XMLTable, since i'm using column, how do i get the data in this format
spacctid spacctcode level legno
-------------------------- ---------- ---------- ---
100000 100000 0 0
NSH nscode 1 1
WXII wxcode 1 2
JOH jhcode 1 3
SNK sncode 1 4
KEN nnnn 1 5
please advise and thanks
regards
wxii
sorry for late reply, the example table consist the xmltype column call nmstruct reside inside bststruct table, all the xml data will
reside in nmstruct column.
the data is as follow:-
<country>
<id>MAL</id>
<sponsornode spacctid="100000" spacctcode="100000" level="0" legno="1">
<sponsornode spacctid="NSH" spacctcode="nscode" level="1" legno="1"/>
<sponsornode spacctid="WXII" spacctcode="wxcode" level="1" legno="2"/>
<sponsornode spacctid="JOH" spacctcode="jhcode" level="1" legno="3"/>
<sponsornode spacctid="SNK" spacctcode="sncode" level="1" legno="4"/>
<sponsornode spacctid="KEN" spacctcode="nnnn" level="1" legno="5"/>
</sponsornode>
</country>
with XMLQuery or XMLTable, since i'm using column, how do i get the data in this format
spacctid spacctcode level legno
--------------------------
100000 100000 0 0
NSH nscode 1 1
WXII wxcode 1 2
JOH jhcode 1 3
SNK sncode 1 4
KEN nnnn 1 5
please advise and thanks
regards
wxii
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks very much, this is exactly what i wanted.
glad I could help
is XDB installed and valid?
if not, install it.
http://www.adp-gmbh.ch/ora/xml_db/install.html
if yes, could be a grant issue.. hard to tell