This discussion is archived
9 Replies Latest reply: Feb 28, 2013 9:39 PM by 993672 RSS

error in xmlfile_shell

993672 Newbie
Currently Being Moderated
Hi,

i create one shell script file in that i wrote one query ,output file is in .xml format.
but while opening the output file i got the following error.

Invalid at the top level of the document. Error processing resource 'file:///C:/Users/Administrator/AppData/Local/Temp/Temp...

ERROR:

Please help me to solve this error.

following is the query

SELECT XMLELEMENT ("DATABASE_OBJECTS",
XMLFOREST (owner,
object_type,
object_name))
FROM all_objects where rownum;
  • 1. Re: error in xmlfile_shell
    AlexAnd Guru
    Currently Being Moderated
    select 
    xmlelement("All", xmlagg( --add
    XMLELEMENT ("DATABASE_OBJECTS",
    XMLFOREST (owner,
    object_type,
    object_name))
    )) --add
    FROM all_objects where rownum<3;
  • 2. Re: error in xmlfile_shell
    993672 Newbie
    Currently Being Moderated
    Hi Thanks for your reply,

    I tried but it is also not working and it gives the following error

    OCI-22303: type "SYS"."XMLTYPE" not found

    this is my sql version.

    SQL*Plus: Release 8.0.6.0.0 - Production on Thu Feb 28 08:55:29 2013

    (c) Copyright 1999 Oracle Corporation. All rights reserved.


    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.6.0 - Production.


    if xmlelement is not wok in this version then let me know how to generate xml file from the above SQL query .
  • 3. Re: error in xmlfile_shell
    993672 Newbie
    Currently Being Moderated
    Hi Thanks for your reply,

    I tried but it is also not working and it gives the following error

    OCI-22303: type "SYS"."XMLTYPE" not found

    this is my sql version.

    SQL*Plus: Release 8.0.6.0.0 - Production on Thu Feb 28 08:55:29 2013

    (c) Copyright 1999 Oracle Corporation. All rights reserved.

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.6.0 - Production.


    if xmlelement is not wok in this version then please telll me how to generate xml file from the above SQL query .
  • 4. Re: error in xmlfile_shell
    odie_63 Guru
    Currently Being Moderated
    Yes, XMLElement is supported in your database version.

    However, the returned datatype (XMLType) is not supported by your outdated version of SQL*Plus.

    Add a .getClobVal() method to convert the XMLType to CLOB, hopefully SQL*Plus will be able to handle it correctly this time :
    select 
    xmlelement("All", 
    xmlagg(
    XMLELEMENT ("DATABASE_OBJECTS",
    XMLFOREST (owner,
    object_type,
    object_name))
    )
    ).getClobVal()
    FROM all_objects 
    where rownum<3;
  • 5. Re: error in xmlfile_shell
    AlexAnd Guru
    Currently Being Moderated
    http://docs.oracle.com/cd/B10500_01/appdev.920/a96620/xdb12gen.htm

    about OCI-22303: type SYS.XMLTYPE not found
    OCI-22303: type SYS.XMLTYPE not found
    ORA-31011: XML parsing failed
  • 6. Re: error in xmlfile_shell
    993672 Newbie
    Currently Being Moderated
    Hi alex,

    I tried using package 'XMLGEN.GETXML' but still i got an error.below is the output of the xmlfile.

    XMLGEN.GETXML('SELECTOWNER"OWNER",OBJECT_TYPE"OBJ_TYPE",OBJECT_NAME"OBJ_NAME",OB
    <?xml version = '1.0'?>
    <ROWSET>
    <ROW num="1">
    <OWNER>APPS</OWNER>

    and below is the modified query: this query is executed in sqldeveloper and got the resul

    SELECT xmlgen.getXml(
    'SELECT owner "OWNER"
    ,object_type "OBJ_TYPE"
    ,object_name "OBJ_NAME"
    ,object_user_name "OBJ_USER_NAME"
    ,physical_name "PHY_NAME"
    from xxhex_custom_objects'
    , 0
    ) FROM dual;

    if i use other than xml file, i got the output but in xmlfile only i didnt got the output.
    Please help me to solve this issue.
  • 7. Re: error in xmlfile_shell
    993672 Newbie
    Currently Being Moderated
    Hi,

    i tried with same query but still i got the problem below is the output of the xml file

    XMLELEMENT("ALL",XMLAGG(XMLELEMENT("DATABASE_OBJECTS",XMLFOREST(OWNER,OBJECT_TYP
    <All><DATABASE_OBJECTS><OWNER>APPS</OWNER><OBJECT_TYPE>FUNCTION</OBJECT_TYPE><
    OB

    Please help me to solve this issue.
  • 8. Re: error in xmlfile_shell
    Jason_(A_Non) Expert
    Currently Being Moderated
    You are going to need to provide more details about this shell script file you are executing and the environment it is in. Beyond being on a Windows box, you have provided none of those details.

    If the SQL statement will execute cleanly in SQLDeveloper but not your script, then the issue lies in the script. How do you know the error is that SQL statement? Can your script even "select * from dual;" successfully?

    Also, SQLDeveloper (which I don't use) may contain its own libraries for accessing the DB. You probably should consider upgrading to a SQL*Plus client that is at least the same version or later as your database.

    Note:
    As I recall, xmlgen in 9.2 is now a synonym for dbms_xmlgen. You should change the package name accordingly [url http://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_xmlge2.htm#1010709]dbms_xmlgen.getXML
  • 9. Re: error in xmlfile_shell
    993672 Newbie
    Currently Being Moderated
    Hi

    i got the reqired output.

    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points