9 Replies Latest reply: Aug 1, 2013 11:02 AM by Jason_(A_Non) RSS

    ORA-19030: Method invalid for non-schema based XML Documents

    47e033bd-d313-47bd-9372-871358ce3c3e

      Hi anybody can u please help me when i am trying to run below query its throwing errror

       

      DECLARE

        doc   xmltype;

      BEGIN

      SELECT

           XMLAGG(XMLElement("Employee",

                      XMLAttributes(

                        'http://www.oracle.com/emp_simple.xsd' AS "xmlns" ,

                        'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",

                        'http://www.oracle.com/emp_simple.xsd

                         http://www.oracle.com/emp_simple.xsd'

                        AS "xsi:schemaLocation"),

                      XMLForest(e.employee_id    AS "EmployeeId",

                                e.last_name      AS "Name",

                                e.job_id         AS "Job",

                                e.manager_id     AS "Manager",

                                e.hire_date      AS "HireDate",

                                e.salary         AS "Salary",

                                e.commission_pct AS "Commission",

                                XMLForest(

                                  d.department_id   AS "DeptNo",

                                  d.department_name AS "DeptName",

                                  d.location_id     AS "Location") AS "Dept")))

          into doc

           FROM employees e, departments d

           WHERE e.department_id = d.department_id;

          

           doc.schemaValidate();

      END;

      /

       

      The error which i am getting is follows

       

      ORA-19030: Method invalid for non-schema based XML Documents

        • 1. Re: Mahesh
          odie_63

          You have to generate a single XML document (with a single root node), here you end up with XML fragments.

           

          What is the root element in the schema? Employee ?

          • 2. Re: Mahesh
            BluShadow

            Mahesh,

             

            Can you please STOP creating your discussions with a subject that just contains your name.

            The point of a subject line is to make it clear to people what the nature of your issue is, so that the correct people can pick it up and look at it, and other people can get on with other things.  To put something like your name in the subject doesn't help people and just wastes their time as they have to open up your question and read it to understand if it's anything they can help with.

             

            BluShadow

            Moderating Team

            • 3. Re: Mahesh
              47e033bd-d313-47bd-9372-871358ce3c3e

              Hi thanks yes my root element is Employee , i am generating as a one file and i am validating with XSD ,

               

              Thats why i used XMLAGG function here

              • 4. Re: Mahesh
                47e033bd-d313-47bd-9372-871358ce3c3e

                please provide some information

                • 5. Re: Mahesh
                  odie_63

                  Hi thanks yes my root element is Employee , i am generating as a one file and i am validating with XSD ,

                  Then you have to validate each <Employee> document individually.

                   

                  Here's a better idea if you need to transfer the collection of employees as a whole :

                  I suggest you modify the schema to have a new root node, say <Employees>, with a collection of <Employee> children under it.

                  Then you would generate something like this :

                  <Employees xmlns="..." xmlns:xsi="..." xsi:schemaLocation="...">

                    <Employee> ... </Employee>

                    <Employee> ... </Employee>

                    <Employee> ... </Employee>

                    ...

                    <Employee> ... </Employee>

                  </Employees>

                  • 6. Re: Mahesh
                    47e033bd-d313-47bd-9372-871358ce3c3e

                    Hi see this is my XSD , you mean to say i need to add one more tag ? as <Employees>??

                     

                     

                    <schema xmlns="http://www.w3.org/2001/XMLSchema"

                                             targetNamespace="http://www.oracle.com/emp_simple.xsd"

                                             version="1.0"

                                             xmlns:xdb="http://xmlns.oracle.com/xdb"

                                             elementFormDefault="qualified">

                                       <element name = "Employee">

                                         <complexType>

                                           <sequence>

                                             <element name = "EmployeeId"

                                                      type = "positiveInteger" minOccurs = "0"/>

                                             <element name = "Name"

                                                      type = "string" minOccurs = "0"/>

                                             <element name = "Job"

                                                      type = "string" minOccurs = "0"/>

                                             <element name = "Manager"

                                                      type = "positiveInteger" minOccurs = "0"/>

                                             <element name = "HireDate"

                                                      type = "date" minOccurs = "0"/>

                                             <element name = "Salary"

                                                      type = "positiveInteger" minOccurs = "0"/>

                                             <element name = "Commission"

                                                      type = "positiveInteger" minOccurs = "0"/>

                                             <element name = "Dept">

                                               <complexType>

                                                 <sequence>

                                                   <element name = "DeptNo"

                                                            type = "positiveInteger" minOccurs = "0"/>

                                                   <element name = "DeptName"

                                                            type = "string" minOccurs = "0"/>

                                                   <element name = "Location"

                                                            type = "positiveInteger" minOccurs = "0"/>

                                                 </sequence>

                                               </complexType>

                                             </element>

                                           </sequence>

                                         </complexType>

                                       </element>

                                     </schema>'

                    • 7. Re: Mahesh
                      47e033bd-d313-47bd-9372-871358ce3c3e

                      And can you please provide me some ideas for declaring the corresponding xsd for this xmltype

                       

                      DECLARE

                        doc   xmltype;

                      BEGIN

                      SELECT

                       

                      Thanks suppose

                       

                      DECLARE

                        doc   xmltype           <<<<<<<<<MyXSD URL HER>>>>>>>;

                      BEGIN

                      SELECT

                      • 8. Re: Mahesh
                        Jason_(A_Non)

                        Yes, Odie meant something like this.

                        <schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:ns1="http://www.oracle.com/emp_simple.xsd" targetNamespace="http://www.oracle.com/emp_simple.xsd" elementFormDefault="qualified" version="1.0">
                            <element name="Employees">
                                <complexType>
                                    <sequence maxOccurs="unbounded">
                                        <element name="Employee">
                                            <complexType>
                                                <sequence>
                                                    <element name="EmployeeId" type="positiveInteger" minOccurs="0"/>
                                                    <element name="Name" type="string" minOccurs="0"/>
                                                    <element name="Job" type="string" minOccurs="0"/>
                                                    <element name="Manager" type="positiveInteger" minOccurs="0"/>
                                                    <element name="HireDate" type="date" minOccurs="0"/>
                                                    <element name="Salary" type="positiveInteger" minOccurs="0"/>
                                                    <element name="Commission" type="positiveInteger" minOccurs="0"/>
                                                    <element name="Dept">
                                                        <complexType>
                                                            <sequence>
                                                                <element name="DeptNo" type="positiveInteger" minOccurs="0"/>
                                                                <element name="DeptName" type="string" minOccurs="0"/>
                                                                <element name="Location" type="positiveInteger" minOccurs="0"/>
                                                            </sequence>
                                                        </complexType>
                                                    </element>
                                                </sequence>
                                            </complexType>
                                        </element>
                                    </sequence>
                                </complexType>
                            </element>
                        </schema>
                        
                        

                         

                        See how the Employee node can now occur many times within the Employees node.