OraFAQ Forum: JDeveloper, Java & XML » XML Query Syntax Problem

Home » Developer & Programmer » JDeveloper, Java & XML » XML Query Syntax Problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

XML Query Syntax Problem [message #273214]

Tue, 09 October 2007 09:47

panzertape
Messages: 20
Registered: August 2007

Junior Member

Hi!
I have a simple XML Query against my DB, but I'm having trouble with the right syntax, because of an ORA-00907 missing right parenthesis error Embarassed

select XMLElement("OBJECTS",
    XMLForest(obj.ID,obj.PID,obj.NAME),
    (
        select XMLAgg(
            XMLElement("GROESSEN",
                XMLForest(gro.ID,gro.WERT)
            )
         )
         from GROESSEN gro where gro.id = obj.id
        
         select XMLAgg(
            XMLElement("OBJ_NAM",   
                XMLForest(objnam.ID,objnam.ID_NAME)
                      )
              )
              from PADBS_OBJ_NAM objnam where objnam.id = obj.id and objnam.id_name = gro.id_name     
    )
) 
from PADBS_OBJECTS obj where obj.ID = 16116

I want to create an output as follows:

<OBJECTS>
   <ID></ID>
   <PID></PID>
   <NAME></NAME>
   <GROESSE>
      <ID></ID>
      <WERT></WERT>
    </GROESSE>
   <OBJ_NAM>
      <ID></ID>
      <ID_NAME></ID_NAME>
   </OBJ_NAM>
</OBJECTS>

But I don't see where I am missing a paranthesis Sad

perhaps you can point me into the right direction. Would be great!

thanks in advance
panzertape

Report message to a moderator

Re: XML Query Syntax Problem [message #273219 is a reply to message #273214]

Tue, 09 October 2007 10:20

Frank
Messages: 7901
Registered: March 2000

Senior Member

from GROESSEN gro where gro.id = obj.id
        
         select XMLAgg(

Something seems to be missing between those two lines

Report message to a moderator

Re: XML Query Syntax Problem [message #273635 is a reply to message #273214]

Thu, 11 October 2007 02:49

panzertape
Messages: 20
Registered: August 2007

Junior Member

Hey Frank,
thanks for your reply!
But what is missing? How should I connect these two Aggregations?
It works if I'm nesting the two XMLAgg. But I want them in the same hierarchical structure, and not nested....

Perhaps XMLConcat? But I don't know how I should do this Sad

any ideas? would be great Razz

Report message to a moderator

Re: XML Query Syntax Problem [message #273767 is a reply to message #273635]

Thu, 11 October 2007 10:56

panzertape
Messages: 20
Registered: August 2007

Junior Member

ok, now I solved this problem by using group by at the end...
But now I have another one...
I just want to add

<?xml version="1.0" encoding="ISO-8859-1"?>

on top of the generated XML-data.

But the XMLRoot()-Function doesn't support the specification of the encode-parameter.

I tried to add

select XMLType ('<?xml version="1.0" encoding="ISO-8859-1"?>' ||

before the XMLElements-Statements but ending up with
ORA-19011: Character string buffer too small
(adding .getClobVal() at the end throws the same error)

Does someone know how I can mange this?

Report message to a moderator

Previous Topic:	Call StoreProcedure
Next Topic:	ORA-01000: maximum open cursors exceeded

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 15:30:33 CDT 2024