Convert XML to UDT in Oracle

Question

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.

Answer 1

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.