Home » Developer & Programmer » JDeveloper, Java & XML » XML Query Syntax Problem
XML Query Syntax Problem [message #273214] Tue, 09 October 2007 09:47 Go to next message
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
Re: XML Query Syntax Problem [message #273219 is a reply to message #273214] Tue, 09 October 2007 10:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
from GROESSEN gro where gro.id = obj.id
        
         select XMLAgg(

Something seems to be missing between those two lines
Re: XML Query Syntax Problem [message #273635 is a reply to message #273214] Thu, 11 October 2007 02:49 Go to previous messageGo to next message
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
Re: XML Query Syntax Problem [message #273767 is a reply to message #273635] Thu, 11 October 2007 10:56 Go to previous message
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?
Previous Topic: Call StoreProcedure
Next Topic: ORA-01000: maximum open cursors exceeded
Goto Forum:
  


Current Time: Fri Dec 02 18:37:45 CST 2016

Total time taken to generate the page: 0.47678 seconds