XML Type conversions in Oracle

Oracle suports XML handling through the built in datatype XMLTYPE. Here is an example of handling XML data to convert xml to object types and viceversa.
Consider the following XML


<customer>
<id>100</id>
<name>XEROX</name>
<country>FRANKFURT</country>
<status>ACTIVE</status>
</customer>


Create a sample object type customer which contains the same elements as the XML data using the following command:
CREATE OR REPLACE TYPE CUSTOMER
AS OBJECT
( ID VARCHAR2(10) ,
NAME VARCHAR2(50) ,
COUNTRY VARCHAR2(50),
STATUS VARCHAR2(15))
/

The following pl/sql block will convert the xml data into one XMLTYPE variable and then populates the object variable v_in which is of type customer and displays the object contents.

DECLARE
v_in customer;
v_xml xmltype;
BEGIN
v_xml :=XMLTYPE('<customer></customer>
<id>100</id><name>XEROX</name><country>GERMANY</country>
<status>ACTIVE</status>'); xmltype.toobject(v_xml,v_in);
DBMS_OUTPUT.PUT_LINE('Customer Id ' v_in.id ' Name 'v_in.name);
END;
Output :
Customer Id 100 Name XEROX
PL/SQL procedure successfully completed

And here, for converting the customer object to XML data, use XMLTYPE() function. See the following pl/sql code:

DECLARE
v_in customer;
v_xml xmltype;
BEGIN
v_in:= new customer('100','XEROX','GERMANY','ACTIVE');
v_xml := xmltype(v_in);
DBMS_OUTPUT.PUT_LINE(v_xml.getclobval());
END;
Output :
<customer><id>100</id><name>XEROX</name>
<country>GERMANY</country><status>ACTIVE</status></customer>
PL/SQL procedure successfully completed