Special Oracle Type XMLTYPE

Example Description

• This is just a small example how to work with the build in Oracle XMLTYPE type.
• Make sure you set up all required Java XML libraries from your Oracle installation.

Important Notes !

• XMLTYPE can be used in select statements, PL/SQL records of typed cursors, fields of object types and elements of collections of type TABLE and VARRAY.
• Have a look at the example download and example setup section to get all required Java XML libraries from your Oracle installation.
• XMLTYPE processing requires Oracle XML libraries. These libraries may collide with other XML libraries. Spring HTTP-Remoting does not run using XMLTYPE with Oracle XML libraries.

Package Specification

PL/SQL Package Specification : XML_TYPE_DEMO

create or replace package xml_type_demo
/**
 * Demo of database type XMLTYPE represented as Java org.w3c.Document. 
 */
as

/**
 * Generates and returns a simple XML-Document.
 *
 * @param xml XML-Document.
 */
procedure get_xml_type
(
  xml out xmltype 
);

/**
 * Parses an XML-Document and returns two extracted fields.
 *
 * @param xml XML-Document to parse.
 * @param name Extracted field 'name' from XML-Document.
 * @param surename Extracted field 'surname' from XML-Document.
 */
procedure extract_from_xml_type
(
  xml       in  xmltype,
  name      out varchar2,
  surename  out varchar2
);

end xml_type_demo;

Package Body

PL/SQL Package Body : XML_TYPE_DEMO

create or replace package body xml_type_demo
as

procedure get_xml_type
(
  xml out xmltype 
)
is
begin
  xml := dbms_xmlgen.getxmltype('select 1 as v1, 1.1 as v2, to_date(''1999/01/01'', ''yyyy/mm/dd'') as v3, ''string_1'' as v4 from dual ' ||
                                 'union select 2, 2.2, to_date(''2000/2/2'', ''yyyy/mm/dd''), ''string_2'' from dual ' ||
                                 'union select 3, 3.3, to_date(''2001/3/3'', ''yyyy/mm/dd''), ''string_3'' from dual');
end get_xml_type;

procedure extract_from_xml_type
(
  xml       in  xmltype,
  name      out varchar2,
  surename  out varchar2
)
is
begin
  select  trim(extractvalue(xml, '/ROW/NAME')),
          trim(extractvalue(xml, '/ROW/SURENAME'))
    into  name,
          surename
    from  dual;
end extract_from_xml_type;

end xml_type_demo;

Factory API : Calling the PL/SQL package

Java Calling Class : XmlTypeDemo.java

package plsql_workbench_examples.factoryapi;

import java.io.ByteArrayInputStream;
import java.io.StringWriter;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;

import factory.ExamplesRPCFactory;
import service.XmlTypeDemoService;
import transferobject.XmlTypeDemoTO;

public class XmlTypeDemo {
  public static void main(String[] args)
  {
    // set database credentials and configuration parameters
    System.setProperty("dbw_examples.url", "jdbc:oracle:thin:@192.168.0.109:1521/orcl");
    System.setProperty("dbw_examples.username", "dbw_examples");
    System.setProperty("dbw_examples.password", "dbw_examples");

    getXmlType();
    extractXmlType();
  }

  private static void getXmlType()
  {
    try {
      // get service
      XmlTypeDemoService service = ExamplesRPCFactory.getXmlTypeDemoService();

      // fetch XML document from stored procedure
      Document doc = service.getXmlType();

      // format output
      Transformer transformer = TransformerFactory.newInstance().newTransformer();
      transformer.setOutputProperty(OutputKeys.INDENT, "yes");
      StreamResult result = new StreamResult(new StringWriter());
      DOMSource source = new DOMSource(doc);
      transformer.transform(source, result);
      String xmlString = result.getWriter().toString();

      System.out.println("fetch xml-document from stored procedure");
      System.out.println(xmlString);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

  private static void extractXmlType()
  {
    try {
      // get service
      XmlTypeDemoService service = ExamplesRPCFactory.getXmlTypeDemoService();

      // build XML document
      DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
      DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
      ByteArrayInputStream bais = new ByteArrayInputStream("<?xml version = '1.0' encoding = 'UTF-8'?><ROW><NAME>Tom</NAME><SURENAME>Jones</SURENAME></ROW>".getBytes("UTF-8"));
      Document doc = dBuilder.parse(bais);

      // call stored procedure with XML document
      XmlTypeDemoTO.ExtractFromXmlTypeTO result = service.extractFromXmlType(doc);

      System.out.println("\n\nextract values from xml-document inside stored procedure");
      System.out.println("name:" + result.name + " / surename:" + result.surename);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}