< Prev - Examples - Next >

Special Oracle Type XMLTYPE

Example Description

Important Notes !

PL/SQL Package Specification : XML_TYPE_DEMO
create or replace package xml_type_demo
as

procedure get_xml_type
(
  xml out xmltype 
);

procedure extract_from_xml_type
(
  xml       in  xmltype,
  name      out varchar2,
  surename  out varchar2
);

end xml_type_demo;
PL/SQL Package Specification : 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;
Java Calling Class : XmlTypeDemo.java
package plsql_workbench_examples;

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) {
    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();
    }
  }
}