xmloracle-databaseplsqlobject-type

How to generate XML from Object Type in Oracle database

As the title explains, how to parse data from object types in Oracle to the XML format?

So for instance, if I have an object type CUSTOMER, how one can convert the data from an instance of that object to XML text?

Solution

  • First you have your object type created:

    CREATE TYPE CUSTOMER AS OBJECT
   (NAME VARCHAR2(100));

    It must be converted to an XMLTYPE, and then you'll be able to get the XML text from it with the getstringval function.

    DECLARE
  v_customer CUSTOMER;
  v_xml XMLTYPE;
BEGIN
  v_customer:= NEW CUSTOMER('Josh');
  v_xml := XMLTYPE(v_customer);
  DBMS_OUTPUT.put_line(v_xml.getstringval);
END;

    This will produce the following output:

    <CUSTOMER><NAME>Josh</NAME></CUSTOMER>