XMLDB : Examples of using getClobVal, getStringVal and getNumberVal

DECLARE
  
v_clob           CLOB;  
v_string        VARCHAR2(4000);
v_number    NUMBER;

BEGIN  

/* To convert the entire xml datatype column into CLOB and display */
    SELECT e.EMP_DETAILS_XML.getClobVal() 
    INTO v_clob
    FROM EMP_XML_DATA e
    WHERE EMPLOYEE_ID = 208;

 --   dbms_output.put_line('v_clob ' || v_clob);

/* To get only part of the XML and convert to CLOB, use extract and getClobVal */    
    SELECT extract(EMP_DETAILS_XML, '/EmployeeInformation/EmployeeDetails').getClobVal() 
    INTO v_clob
    FROM EMP_XML_DATA e
    WHERE EMPLOYEE_ID = 208;
    
    dbms_output.put_line('v_clob ' || v_clob);
END;

Output of getClobVal to fetch only EmployeeDetails

 --------------------------------------------------------------

<EmployeeDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <name>William Gietz</name>
    <email>wgietz2@oramail.com</email>
    <telephone>515.123.8181</telephone>
    <job>Public Accountant</job>
    <salary>8390</salary>
    <hire_date>24-AUG-2001</hire_date>
  </EmployeeDetails>  

To print a particular string element of an XML datatype you can extract that element using extract and getStringVal as shown below

SELECT extract(EMP_DETAILS_XML, '/EmployeeInformation/EmployeeDetails/name/text()').getStringVal()
INTO v_string 
FROM EMP_XML_DATA
WHERE existsNode(EMP_DETAILS_XML, '/EmployeeInformation/EmployeeDetails[telephone="515.123.8181"]') = 1
AND EMPLOYEE_ID =  208;

dbms_output.put_line('v_string ' || v_string);

Output of getStringVal 

---------------------------

William Gietz

To print a particular numeric element of an XML dataype, use extract and getNumberVal

DECLARE
v_number  NUMBER;

BEGIN 
    SELECT extract(EMP_DETAILS_XML, '/EmployeeInformation/EmployeeDetails/salary/text()').getNumberVal()
    INTO v_number 
      FROM EMP_XML_DATA
      WHERE existsNode(EMP_DETAILS_XML, '/EmployeeInformation/EmployeeDetails[telephone="515.123.8181"]') = 1
    AND EMPLOYEE_ID =  208;
    
    dbms_output.put_line('v_number ' || v_number);
    
END;

Output of getNumberVal 

------------------------------

8390

Below are some standard oracle util procedures for validating XML data

isSchemaBased -  Returns TRUE if the XMLType instance is based on an XML schema, FALSE otherwise.

schemaValidate() -- Validates the input instance according to the XML Schema. Raises error if the input instance is non-schema based.  

If validation fails an error is raised; 

else, the document''s status is changed to validated.

MEMBER PROCEDURE schemaValidate(self IF OUT NOCOPY XMLType);

                 self (OUT) XML instance being validated against the schema. 

 

isSchemaValid() -- Checks if the input instance is schema valid according to the given schema URL.

   member function isSchemaValid(

                schurl IN VARCHAR2 := NULL, 

                elem IN VARCHAR2 := NULL)

  return NUMBER deterministic;

Parameter IN / OUT Description

schurl         (IN)                The URL of the XML Schema against which to check conformance.

elem         (IN)                Element of a specified schema, against which to validate. 

                                               This is useful when we have a XML Schema which defines more than one

                                               top level element,  and we want to check conformance against a 

                                               specific one of these elements.

isSchemaValidated() -- Checks if the instance has been validated against the schema

Returns 1 if the instance has been validated against the schema, 0 otherwise.

setSchemaValidated() -- Sets the schema valid flag to avoid costly schema validation.

These functions validate an XMLType instance against a registered XML schema.

MEMBER PROCEDURE setSchemaValidated(

   self IF OUT NOCOPY XMLType,

   flag IN BINARY_INTEGER := 1);

Parameter IN / OUT Description

self                  (OUT)         XML instance.

flag                  (IN)         0 - NOT VALIDATED; 1 - VALIDATED (Default)   

Transform - applies XSL stylesheet to transform an xml document to a more readable format like html.

for a detailed example you can refer to subsequent posts.

Alternatively you can also use DBMS_XSLPROCESSOR to transform the xml document. Using this package requires a lot more development effort to achieve the same result.

For an example of the same you can refer to

For DBMS_XSLPROCESSOR 

refer http://www.oraclefrontovik.com/2012/12/some-different-ways-to-perform-xslt-from-plsql/

toObject Member procedure

converts XML string to Object Types using XMLSCHEMA mapping available. 

If a schema is not supplied or the input is a non schema based xml, 

the procedure uses cannonical mapping between elements and object type attributes.

This is a very common application requirement, where you may get an input XML type parameter, and you want to convert it into the corresponding oracle object datatype before doing any data manipulations. Once the data is manipulated you may want to reconvert the object into an XML datatype and return it to the calling routing. This can be done using sys_XMLGEN function as shown below.

Below is an example

CREATE OR REPLACE TYPE TYP_EMP_OBJ IS OBJECT
(
    employee_id      NUMBER,
    employee_name    VARCHAR2(100),
    date_of_birth    DATE,
    gender           VARCHAR2(1),
    department_id    NUMBER,
    designation      VARCHAR2(100)	
);

DECLARE

v_xml_str                       VARCHAR2(1000);
v_xml                                     xmltype;
v_new_xml                             xmltype;

v_emp_details_obj          TYP_EMP_OBJ;
v_employee_id_xml             xmltype;

BEGIN
   
      v_xml_str := '   110001  Stephen King110001  21-JUL-62  M  10  CEO ';
      
       SELECT XMLTYPE(
         v_xml_str
       )
       INTO v_xml
       FROM DUAL;
             
--       dbms_output.put_line(l_xml.getStringVal());
             
       v_xml.toobject(v_emp_details_obj);
             
            dbms_output.put_line(v_emp_details_obj.employee_id);
            dbms_output.put_line(v_emp_details_obj.employee_name);
            
       v_emp_details_obj.department_id := 11;
       v_emp_details_obj.designation := 'CTO';

      SELECT sys_XMLGEN(v_emp_details_obj, XMLFormat('emp_details'))
      INTO v_new_xml
      from   dual;
      
      dbms_output.put_line(v_new_xml.getStringVal());
      
--If you want to select only a particular element use extract	  
      SELECT sys_XMLGen(v_emp_details_obj, XMLFormat('emp_details')).extract('/emp_details/EMPLOYEE_ID')
      INTO v_employee_id_xml
        from   dual;
        
      dbms_output.put_line('Employee Id :' ||   v_employee_id_xml.getStringVal());
END;

XMLTYPE - member function/constructor --  XMLTYPE is the datatype used to store XML data. The type constructor accepts different input like character, binary or ref cursor, clob, bfile, blob, object types. Example of some of these datatype constructors are given below example of character input to XMLTYPE constructor. The input character string should be a properly formed xml string.

SELECT
   XMLTYPE 
   (
      'scott'
   ) as xml_typ
FROM
   DUAL;   

output
------
scott

example of binary input to XMLTYPE constructor.

SELECT
   XMLTYPE
   (
      TO_BLOB
      (
         HEXTORAW ('3C696D673E3A2D293C2F696D673E')
      ),
      NLS_CHARSET_ID('WE8MSWIN1252')
   ) as xml_typ
FROM
   DUAL;

xml_typ
:-)

example of passing ref cursor as input parameter

SELECT
   XMLTYPE
   (
      CURSOR
      (            
         SELECT
            *
         FROM
            DEPARTMENTS
      )
   )
FROM
   DUAL;

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPARTMENT_ID>10</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
  <MANAGER_ID>200</MANAGER_ID>
  <LOCATION_ID>1700</LOCATION_ID>
 </ROW>
 <ROW>
  <DEPARTMENT_ID>20</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
  <MANAGER_ID>201</MANAGER_ID>
  <LOCATION_ID>1800</LOCATION_ID>
 </ROW>
</ROWSET>

example of passing a bfile as input parameter when inserting into an XMLTYPE column

INSERT INTO EMP_XML_DATA (EMPLOYEE_ID, EMP_DETAILS_XML,INSERT_DATE,INSERT_USER_ID)
  VALUES (211,XMLType(bfilename('XMLDIR', 'EmployeeDetails.xml'),nls_charset_id('AL32UTF8')),SYSDATE,10);