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

5 comments :

  1. What happens if one has a collection in the object type? Or if there are repeating elements in the XML which must be stored in a collection in the object type?

    ReplyDelete
  2. plus the XML is invalid and pipes are missing in the dbms_output.put_line... perfect example ...

    ReplyDelete
  3. How do you handle MIXED content? It gets stored in SYS_XDBPD$ column of object type, but how do you get it back into XML document?

    ReplyDelete