3 Replies Latest reply: Dec 20, 2012 2:54 AM by odie_63 RSS

    XMLType.ToObject()

    650745
      Hi there,

      I am running into a problem when (in Oracle9i Enterprise Edition Release 9.2.0.6.0) trying to map an XML document contained in an XMLType variable to a corresponding object-type variable using the XMLType.ToObject() function.

      The problem occurs when the XML contains "repeating" fragments.
      I do not know how to define the corresponding object-type in such a way that XMLType.ToObject() does not run into error "ORA-19031: XML element or attribute ... does not match any in type ...".

      For example the following XML:
      ====================
      <MyObject>
      <ELEMENT01>VALUE01</ELEMENT01>
      <ELEMENT02>VALUE02</ELEMENT02>
      <MYGROUP>
      <GROUPELEMENT01>VALUEG01E01</GROUPELEMENT01>
      <GROUPELEMENT02>VALUEG01E02</GROUPELEMENT02>
      </MYGROUP>
      <MYGROUP>
      <GROUPELEMENT01>VALUEG02E01</GROUPELEMENT01>
      <GROUPELEMENT02>VALUEG02E02</GROUPELEMENT02>
      </MYGROUP>
      </MyObject>
      ====================

      The corresponding object-type that I defined is as follows:
      ====================
      create or replace type TP_MYGROUP_REC as object
      (GROUPELEMENT01 varchar2(50)
      ,GROUPELEMENT02 varchar2(50)
      );
      /
      create or replace type TP_MYGROUP_TAB as table of TP_MYGROUP_REC;
      /
      create or replace type TP_MYOBJECT as object
      (ELEMENT01 varchar2(50)
      ,ELEMENT02 varchar2(50)
      ,MYGROUP TP_MYGROUP_TAB
      );
      /
      ====================

      I tested with the following script:
      ====================
      declare
      l_xmltype xmltype;
      l_myobject TP_MYOBJECT;
      begin
      l_xmltype := XMLTYPE
      ('<MyObject>
      <ELEMENT01>VALUE01</ELEMENT01>
      <ELEMENT02>VALUE02</ELEMENT02>
      <MYGROUP>
      <GROUPELEMENT01>VALUEG01E01</GROUPELEMENT01>
      <GROUPELEMENT02>VALUEG01E02</GROUPELEMENT02>
      </MYGROUP>
      <MYGROUP>
      <GROUPELEMENT01>VALUEG02E01</GROUPELEMENT01>
      <GROUPELEMENT02>VALUEG02E02</GROUPELEMENT02>
      </MYGROUP>
      </MyObject>');

      l_xmltype.ToObject(l_myobject);
      end;
      /
      ====================
      This results in: ORA-19031: XML element or attribute GROUPELEMENT01 does not match any in type TP_MYGROUP_REC.

      Does anyone out there know how to define the object-type(s) in such a way that I can cast this type of XML to an object using XMLType.ToObject() ?

      Thanks a lot for your reaction,
      Jaap Kool
        • 1. Re: XMLType.ToObject()
          657544
          Hi,

          declare

          begin
          obj_t type;
          textxml xmltype;

          begin

          SELECT xmltype(xml) INTO l_xml
          FROM xxxml_test_table
          WHERE process_nm = 'TEST1'
          AND status_cd = 'P'
          AND record_no = p_record_no;

          testxml.toobject(obj, 'TEST1', 'PurchaseOrder');
          end;

          here TEST1 is xml schema and PurchaseOrder is schema elemnet.
          In the above program the testxml.toobject is failing , I am getting the ora-01722:invalid number.

          Can any one provide the solution.

          Regards,
          Kishore
          • 2. Re: XMLType.ToObject()
            980942
            Dear Jaap,

            Currently I'm having exactly the same problem that you faced almost 4 years ago :) .... Have been looking around for documentation and forums but haven't found the answer so far. We are using Oracle 11g (11.2.0.2.0) and when trying to execute the sample you post then the same error is shown.

            Please, any help from your side would be highly appreciated.

            Hernan
            • 3. Re: XMLType.ToObject()
              odie_63
              In absence of an XML schema, Oracle uses a canonical mapping between SQL objects and XML.

              For instance, the XML structure corresponding to the object hierarchy defined in the first post is :
              <TP_MYOBJECT>
                <ELEMENT01>VALUE01</ELEMENT01>
                <ELEMENT02>VALUE02</ELEMENT02>
                <MYGROUP>
                  <TP_MYGROUP_REC>
                    <GROUPELEMENT01>VALUEG01E01</GROUPELEMENT01>
                    <GROUPELEMENT02>VALUEG01E02</GROUPELEMENT02>
                  </TP_MYGROUP_REC>
                  <TP_MYGROUP_REC>
                    <GROUPELEMENT01>VALUEG02E01</GROUPELEMENT01>
                    <GROUPELEMENT02>VALUEG02E02</GROUPELEMENT02>
                  </TP_MYGROUP_REC>
                </MYGROUP>
              </TP_MYOBJECT>
              Note the additional "TP_MYGROUP_REC" element that encloses the two leaf values.

              With that input, this works :
              SQL> declare
                2    l_xmltype  xmltype;
                3    l_myobject TP_MYOBJECT;
                4  begin
                5    l_xmltype := XMLTYPE(
                6  '<TP_MYOBJECT>
                7    <ELEMENT01>VALUE01</ELEMENT01>
                8    <ELEMENT02>VALUE02</ELEMENT02>
                9    <MYGROUP>
               10      <TP_MYGROUP_REC>
               11        <GROUPELEMENT01>VALUEG01E01</GROUPELEMENT01>
               12        <GROUPELEMENT02>VALUEG01E02</GROUPELEMENT02>
               13      </TP_MYGROUP_REC>
               14      <TP_MYGROUP_REC>
               15        <GROUPELEMENT01>VALUEG02E01</GROUPELEMENT01>
               16        <GROUPELEMENT02>VALUEG02E02</GROUPELEMENT02>
               17      </TP_MYGROUP_REC>
               18    </MYGROUP>
               19  </TP_MYOBJECT>');
               20  
               21    l_xmltype.ToObject(l_myobject);
               22  
               23    dbms_output.put_line(l_myobject.mygroup(1).groupelement01);
               24  
               25  end;
               26  /
               
              VALUEG01E01
               
              PL/SQL procedure successfully completed
               
              Here's the approach you can follow to achieve a "custom" mapping :
              {thread:id=2475819}
              and,
              {message:id=10712117}

              Edited by: odie_63 on 20 déc. 2012 09:54