Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to retreive one row (node) from a XMLTYPE table

946607Jun 29 2012 — edited Jun 29 2012
Hi.
We need to store a big XML file into a table so we can make some queries to it later. The XML file will be very big, with lots of different nodes with the same structure. I'll write an intro with the followed steps and the question is at the end of the post:

h3. Intro
<Root>
<Row>
<Id>persona5</Id>
<Out>0</Out>
<Name>Julio Cesar</Name>
<Entity_type>NATURAL</Entity_type>
</Row>
<Row>
<Id>persona6</Id>
<Out>0</Out>
<Name>Julio Cesar</Name>
<Entity_type>FISICA</Entity_type>
<Identification_doc>3710619</Identification_doc>
<TypeIdentification_doc>OTROSFISICA</TypeIdentification_doc>
<Birth_date>14611</Birth_date>
<Nationality>CO</Nationality>
</Row>
<Row>
<Id>persona7</Id>
<Out>0</Out>
<Name>Orient Star Corporation</Name>
<Entity_type>ARTIFICIAL</Entity_type>
</Row>
</Root>


So, I've registered its schema:
Begin
DBMS_XMLSCHEMA.registerSchema(
schemaURL => 'http://www.oradev.com/sample.xsd',
schemaDoc => bfilename('TESTS','sample.xsd'));
End;

And created a XMLTYPE table to store it:
Create table XML_TEST of XMLType XMLSCHEMA "http://www.oradev.com/sample.xsd" ELEMENT "Root";

insert into XML_TEST
values
(
XMLTYPE
(
Bfilename ('TESTS', 'sample.xml'),
nls_charset_id('AL32UTF8')
));

h3. Question:
I would like to retrieve the data of one of the nodes at the XML. In pseudocode, let's say I would like to make something like:
select out, name, entitytype from XML_TEST where id = 'persona5';

To get this I've tried the following query:
select
extractValue(SYS_NC_ROWINFO$, '/Root/Row/Out'),
extractValue(SYS_NC_ROWINFO$, '/Root/Row/Name'),
extractValue(SYS_NC_ROWINFO$, '/Root/Row/Entity_Type')
from XML_TEST where existsNode(SYS_NC_ROWINFO$, '/Root/Row[Id="persona5"= 1;

but I get the followin error, although +select count(*) from XML_TEST where existsNode(SYS_NC_ROWINFO$, '/Root/Row[Id="persona5"= 1;+ returns 1.

ORA-01427: single-row subquery returns more than one row
*01427. 00000 - "single-row subquery returns more than one row"*

How should I make this subquery?

Thanks in advance
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 27 2012
Added on Jun 29 2012
7 comments
815 views