9 Replies Latest reply: Feb 28, 2013 11:39 PM by 993672 RSS

    error in xmlfile_shell

    993672
      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
          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
            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
              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
                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
                  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
                    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
                      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)
                        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
                          Hi

                          i got the reqired output.

                          Thanks