This discussion is archived
3 Replies Latest reply: Dec 20, 2012 12:54 AM by odie_63 RSS

XMLType.ToObject()

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

Legend

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