Convert XML to UDT in Oracle

Is there an easy way to convert an XMLType to a User Defined Type? I can convert the UDT to XMLType using the below.

select SYS_XMLGEN(pUDT) into param2 from dual;

I can't though, 开发者_开发技巧is find a function that takes that and turns it back into that UDT using the same mappings the SYS_XMLGEN used.

I know this is old, but there is a way to convert from XMLType back into a UDT. It will use cannonical mapping if no schema provided.

while Gary's point is definitely valid, you may have to custom code it if it gets quite complicated, but it is possible to do it simply, assuming you have a simple object! (I have not played around with the schema mapping nor the advance features.

xmlType.ToObject

create OR REPLACE type udtToXmlAndBack AS OBJECT(AA varchar2(50) , BB NUMBER);

variable x refcursor

SET SERVEROUTPUT ON
DECLARE 
    pUDT udtToXmlAndBack;
    newUDTFromXml udtToXmlAndBack;
    xData xmlType ;
BEGIN
    pUDT := NEW udtToXmlAndBack('ABC',10) ;
    DBMS_OUTPUT.PUT_LINE('pUDT.AA = ' || pUDT.AA || '; pUDT.BB = ' || pUDT.BB); 
    SELECT SYS_XMLGEN(pUDT) into xData FROM DUAL;
    xData.toobject(newUDTFromXml);
    DBMS_OUTPUT.PUT_LINE('newUDTFromXml.AA = ' || newUDTFromXml.AA || '; newUDTFromXml.BB = ' || newUDTFromXml.BB); 
    open :x for
    select pUDT initUDT, xData xmlData,newUDTFromXml udtFromXML from dual;
END ;
/

PRINT :X;

and now the output:

anonymous block completed
pUDT.AA = ABC; pUDT.BB = 10
newUDTFromXml.AA = ABC; newUDTFromXml.BB = 10

X
------------------------------------
INITUDT                             
------------------------------------
mySchema.UDTTOXMLANDBACK('ABC',10)       

--------------------
xmlData   
--------------------
<?xml version="1.0"?>
<ROW>
 <AA>ABC</AA>
 <BB>10</BB>
</ROW>

--------------------
UDTFROMXML          
--------------------
mySchema.UDTTOXMLANDBACK('ABC',10)

This is using the 'toObject' off of the XmlType. Here are a few other links that I found on this topic: http://technology.amis.nl/blog/6131/oracle-sql-and-plsql-juggling-with-user-defined-types-adt-and-xmltype-for-creating-an-adt-based-xml-service-api & http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:19049264697122

edit

this is more for me than anyone else {since the initial answer is 'it cannot' --> which is can, even for complex types}, but I have had to use the xmltype.toobject with schema on a quite complicated xml/xsd document and it worked as advertised (including the creation of array types (and type within type within type etc)

xx.toobject(xxx,'http://schema/doc','RootDoc');

I would argue that technically you can't.

For example

select sys_xmlgen(mdsys.sdo_geometry(1,2,mdsys.sdo_point_type(1,2,3),null,null)) 
from dual;

returns

<?xml version="1.0"?>
<ROW>
  <SDO_GTYPE>1</SDO_GTYPE>
  <SDO_SRID>2</SDO_SRID>
  <SDO_POINT>
    <X>1</X>
    <Y>2</Y>
    <Z>3</Z>
  </SDO_POINT>
</ROW>

and there's nothing in the XML to indicate either (a) it is a mdsys.sdo_geometry type, or (b) the sub-objects SDO_GTYPE etc are in the MDSYS schema.

I think you'd want your own methods on the object type (or you own functions if you don't have control of the object code, like MDSYS) that converts the object to/from XML. That said, I'd probably start with the output of SYS_XMLGEN.