This discussion is archived
9 Replies Latest reply: Aug 1, 2013 9:02 AM by Jason_(A_Non) RSS

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

47e033bd-d313-47bd-9372-871358ce3c3e Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    please provide some information

  • 5. Re: Mahesh
    odie_63 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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) Expert
    Currently Being Moderated

    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.

Legend

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