Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

XMLTYPE Compatibility Guide

Document Type | Technical Information

Category | Migration

Applicable Product Versions | 7FS02, 7FS02PS

Document Number | TMITI043

 

Overview

XMLTYPE provided by Tibero is a data type for processing XML data and is internally offered in two ways: CLOB-based and OBJECT-based.

When migrating databases, to maintain the XMLTYPE data type consistently in both the AS-IS and TO-BE environments, the CLOB-based XMLTYPE must be converted to the OBJECT-based XMLTYPE. In Company O, XMLTYPE is stored by default as an OBJECT type, so conversion is required to match this when migrating to Tibero.

 

 

Method

How to Check XMLTYPE

$ cd $TB_HOME/scripts/pkg

$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL @xmltype_install_checker.sql
XMLTYPE OID(00000000000000000000000000000001) IS VALID

===XMLTYPE(CLOB) DEPENDENCY LIST===


===================================

PLEASE CHECK DEPENDENCY LIST.
DEPENDENCY LIST WILL BE INVALIDATED IF YOU INSTALL XMLTYPE(OBJECT)
YOU CAN INSTALL XMLTYPE(OBJECT)!!

PSM completed.

 

How to Change to XMLTYPE OBJECT TYPE

$ cd $TB_HOME/scripts/pkg

$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL @install_xmltype.sql

Type 'XMLTYPE' created.

Type 'XMLSEQUENCETYPE' created.

Package 'STANDARD_EXTENSION_XML' created.

System altered.

Type 'SQLPROF_ATTR' created.

Synonym 'SQLPROF_ATTR' created.

Granted.

Package 'DBMS_SQLTUNE' created.

Synonym 'DBMS_SQLTUNE' created.

...
Package 'SYS.DBMS_METADATA' altered.

Package 'SYS.DBMS_XMLGEN' altered.

Package 'SYS.DBMS_XMLPARSER' altered.

Package 'SYS.DBMS_XSLPROCESSOR' altered.

SQL

 

Confirm Change to OBJECT TYPE

$ cd $TB_HOME/scripts/pkg

$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL @xmltype_install_checker.sql
===XMLTYPE(OBJECT) IS ALREADY INSTALLED===

===XMLTYPE(OBJECT) DEPENDENCY LIST===


===================================

PLEASE CHECK DEPENDENCY LIST.
DEPENDENCY LIST WILL BE INVALIDATED IF YOU UNINSTALL XMLTYPE(OBJECT)
YOU CAN UNINSTALL XMLTYPE(OBJECT)!!

PSM completed.

 

How to Change XMLTYPE Interface

XMLTYPE exists in both CLOB form and OBJECT form. The XMLTYPE CLOB form is implemented in the same way as CLOB, and the XMLTYPE OBJECT form is created through an OBJECT TYPE creation DDL similar to the following. The XMLTYPE OBJECT form contains a CLOB inside the OBJECT, and the XMLTYPE is stored in that CLOB.

CREATE OR REPLACE TYPE XMLTYPE AS OBJECT( 
Doc CLOB, 
โ€ฆ 
); 
/

Although the final storage is in the CLOB, so it may appear the same, because it is implemented as an OBJECT TYPE, it has the characteristics of an OBJECT and contains member functions within the TYPE, allowing calls to member functions from the OBJECT TYPE.

Therefore, regarding the XMLTYPE OBJECT Interface, it has the characteristics of an OBJECT and allows calling member functions directly from the TYPE. When XMLTYPE is installed as OBJECT in ORACLE or TIBERO, XMLTYPE can be used by utilizing the OBJECT Interface.

Next, regarding the XMLTYPE CLOB Interface, the existing TIBERO supported XMLTYPE as CLOB. Since it is the same as CLOB, the calling method of the OBJECT form cannot be used, so it is implemented using STANDARD function calls. In the OBJECT Interface, XMLTYPE member functions or XMLTYPE subprograms (https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/t_xml.htm#BABBJDAH) are called as shown below.

[SQL] 
create table t(c1 xmltype); 
Insert into t values (xmltype(โ€˜<example/>โ€™)); 
Select x.c1.getclobval() from t x; 
Select x.c1.existsnode(โ€˜/exampleโ€™) from t x; 
[PSM] 
declare 
x xmltype; 
Begin 
x := xmltype(โ€˜<example/>โ€™); 
dbms_output.put_line(x.getclobval()): 
end; 
/

 

Table 1. OBJECT Interface Method

That is, subprograms inside XMLTYPE are called through โ€˜.โ€™. However, if it is not an object interface and XMLTYPE is implemented as CLOB, calling as above results in a strange call where subprograms inside XMLTYPE are being called through โ€˜.โ€™ on a CLOB.

Therefore, since such calling cannot be used when in CLOB state, the CLOB Interface calls subprograms using STANDARD function calls, passing XMLTYPE as the first argument. Hence, the XMLTYPE OBJECT INTERFACE can be converted to CLOB Interface as follows.

[SQL] 
create table t(c1 xmltype); 
Insert into t values (xmltype(โ€˜<example/>โ€™)); 
Select /*x.c1.getclobval()->*/ getclobval(x.c1) from t x; 
Select /*x.c1.existsnode(โ€˜/exampleโ€™)->*/ existsnode(x.c1)  from t x; 
[PSM] 
declare 
x xmltype; 
Begin 
x := xmltype(โ€˜<example/>โ€™); 
dbms_output.put_line(getclobval(x)): 
end; 
/

 

Table 2. CLOB Interface Method

Therefore, the method for converting XMLTYPE OBJECT Interface to XMLTYPE CLOB Interface is

to change INSTANCE.FUNCTION(โ€ฆ) to FUNCTION(INSTANCE, โ€ฆ).

 

Exception CASE1) Functions with Different Names

For the TRANSFORM and EXTRACT functions, if provided as general STANDARD functions, they conflict with functions of the same name, so they must be changed to similar function names such as XMLTRANSFORM and EXTRACT_XML when called.

[Object Interface]

Declare x xmltype := โ€˜โ€ฆ.โ€™;

xsl xmltype; := โ€˜โ€ฆ.โ€™;

res xmltype;

Begin

res := x.transform(xsl);

res := res.extract(โ€˜/element1โ€™);

dbms_output.put_line(res.getstringval());

End;

/

[Clob Interface]

Declare x xmltype := โ€˜โ€ฆ.โ€™;

xsl xmltype; := โ€˜โ€ฆ.โ€™;

res xmltype;

Begin

res := xmltransform(x, xsl);

res := extract_xml(res, โ€˜/element1โ€™);

 dbms_output.put_line(res);

End;

/

 

Exception CASE2) Differences When Using TABLE Function

When used with TABLE, OBJECT TYPE uses a function called value to output only the value of the OBJECT, but since CLOB is not an OBJECT, using the value function causes an error. Therefore, generally, output the result of the table function as if primitive type columns are output.

In fact, from the perspective of the TABLE function, using the value function with OBJECT TYPE is an exceptional case, so when XMLTYPE is CLOB TYPE, you can change it as in the example below.

[Object Interface]

Select extract(value(v), โ€˜/elementโ€™,

 โ€˜xmlns=http://www.loc.gov/MARC21/slimโ€™

 ).getstringval

from xml_table a,

table(xmlsequence

( extract(a.xml_column, โ€˜/f/aโ€™,

 โ€˜xmlns=http://www.loc.gov/MARC21/slimโ€™))) v;

[Clob Interface]

Select extract(v.column_value, โ€˜/elementโ€™,

โ€˜xmlns=http://www.loc.gov/MARC21/slimโ€™)

from xml_table a,

table(xmlsequence( extract(a.xml_column, โ€˜/f/aโ€™,

โ€˜xmlns=http://www.loc.gov/MARC21/slimโ€™))) v;

Exception CASE3) SYS.XMLTYPE Inside PSM

The reason is very simple. When using CLOB TYPE, no one uses SYS.CLOB, and that functionality does not work. Therefore, when XMLTYPE is a CLOB TYPE primitive type, SYS.XMLTYPE must be called as XMLTYPE.

[Object Interface]

create or replace function func3 (a sys.xmltype)

 return sys.xmltype as

begin

   return null;

end;

/

[Clob Interface]

create or replace function func3 (a xmltype)

 return xmltype as

begin

   return null;

end;

/

 

Exception CASE3) SQL Functions

SQL functions that are not member functions are not called using the โ€˜.โ€™ operator, so they do not need to be converted.

XML Function IN SQL REFERENCE: https://docs.oracle.com/database/121/SQLRF/functions002.htm#SQLRF51185

 

Note

GETCLOBVAL is a function that retrieves XMLTYPE as CLOB TYPE. In the CLOB INTERFACE, since XMLTYPE is already CLOB, this function does not need to be used.

ex)  select getclobval(x.c1) from t;  
- select x.c1 from t;

 

Execution Test

OBJECT_TYPE->CLOB

$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL @xmltype_install_checker
===XMLTYPE(OBJECT) IS ALREADY INSTALLED===

===XMLTYPE(OBJECT) DEPENDENCY LIST===

TIBERO.PO_XML_TAB(TABLE)

===================================

PLEASE CHECK DEPENDENCY LIST.
DEPENDENCY LIST WILL BE INVALIDATED IF YOU UNINSTALL XMLTYPE(OBJECT)
@@@@@@@@@@@@@@@@@@ ERROR: PLEASE DROP TABLES RELATED WITH XMLTYPE!! @@@@@@@@@@@@@@@@@@


SQL desc PO_XML_TAB;
COLUMN_NAME         TYPE               CONSTRAINT
------------------- ------------------ ---------------
POID                NUMBER
PODOC               XMLTYPE                                                                                                                                                                                XMLTYPE

SQL create table tmp_po_xml_tab as select POID, x.podoc.getclobval() as PODOC from 
po_xml_tab x;    2 

Table 'TMP_PO_XML_TAB' created.

SQL desc TMP_PO_XML_TAB;
COLUMN_NAME         TYPE               CONSTRAINT
------------------- ------------------ ---------------
POID                NUMBER
PODOC               CLOB

-- Drop existing xml table 
SQL drop table po_xml_tab; 
Table 'PO_XML_TAB' dropped.


SQL @xmltype_install_checker.sql
===XMLTYPE(OBJECT) IS ALREADY INSTALLED===

===XMLTYPE(OBJECT) DEPENDENCY LIST===


===================================

PLEASE CHECK DEPENDENCY LIST.
DEPENDENCY LIST WILL BE INVALIDATED IF YOU UNINSTALL XMLTYPE(OBJECT)
YOU CAN UNINSTALL XMLTYPE(OBJECT)!!

PSM completed.

SQL @uninstall_xmltype.sql

Synonym 'XMLTYPE' dropped.

Type 'XMLSEQUENCETYPE' dropped.

Type 'XMLTYPE' dropped.

Package 'STANDARD_EXTENSION' created.

...
Package 'SYS.DBMS_XMLGEN' altered.

Package 'SYS.DBMS_XMLPARSER' altered.

Package 'SYS.DBMS_XSLPROCESSOR' altered.

SQL @xmltype_install_checker.sql
XMLTYPE OID(00000000000000000000000000000001) IS VALID

===XMLTYPE(CLOB) DEPENDENCY LIST===


===================================

PLEASE CHECK DEPENDENCY LIST.
DEPENDENCY LIST WILL BE INVALIDATED IF YOU INSTALL XMLTYPE(OBJECT)
YOU CAN INSTALL XMLTYPE(OBJECT)!!

PSM completed.

SQL conn tibero/tmax
Connected to Tibero.

SQL create table po_xml_tab (POID number, PODOC xmltype);

Table 'PO_XML_TAB' created.

SQL  insert into po_xml_tab select POID, PODOC from tmp_po_xml_tab; 

1 row inserted.

SQL commit;

Commit completed.

SQL conn sys/tibero
Connected to Tibero.

SQL exec UTL_RECOMP.RECOMP_SERIAL('TIBERO'); 

PSM completed.

SQL conn tibero/tmax
Connected to Tibero.

SQL select * from PO_XML_TAB;

      POID PODOC
---------- --------------------------------------------------------------------------------
       100
                  1
                    Po_1
                    Jo


1 row selected.

 

CLOB->OBJECT TYPE

$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL @xmltype_install_checker
XMLTYPE OID(00000000000000000000000000000001) IS VALID

===XMLTYPE(CLOB) DEPENDENCY LIST===


===================================

PLEASE CHECK DEPENDENCY LIST.
DEPENDENCY LIST WILL BE INVALIDATED IF YOU INSTALL XMLTYPE(OBJECT)
YOU CAN INSTALL XMLTYPE(OBJECT)!!

PSM completed.

$ tbsql tibero/tmax

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL CREATE TABLE po_xml_tab( 
        poid number, 
        poDoc XMLTYPE 
     );    2    3    4 

Table 'PO_XML_TAB' created.

SQL INSERT INTO po_xml_tab VALUES 
    (100, XMLType(' 
       1 
         Po_1 
         John 
          
             1033, Main Street 
             Sunnyvalue 
             CA 
         
     ')) 
/   2    3    4    5    6    7    8    9   10   11   12 

1 row inserted.

SQL conn sys/tibero
Connected to Tibero.

SQL @xmltype_install_checker
XMLTYPE OID(00000000000000000000000000000001) IS VALID

===XMLTYPE(CLOB) DEPENDENCY LIST===

TIBERO.PO_XML_TAB(TABLE)

===================================

PLEASE CHECK DEPENDENCY LIST.
DEPENDENCY LIST WILL BE INVALIDATED IF YOU INSTALL XMLTYPE(OBJECT)
@@@@@@@@@@@@@@@@@@ ERROR: PLEASE DROP TABLES RELATED WITH XMLTYPE!! @@@@@@@@@@@@@@@@@@

PSM completed.

SQL conn tibero/tmax
Connected to Tibero.

SQL  create table tmp_po_xml_tab as select poid, getclobval(PODOC) PODOC from po_xml_tab; 

Table 'TMP_PO_XML_TAB' created.

SQL  desc tmp_po_xml_tab

COLUMN_NAME         TYPE               CONSTRAINT
------------------- ------------------ ---------------
POID                NUMBER
PODOC               CLOB

SQL drop table PO_XML_TAB;

Table 'PO_XML_TAB' dropped.

-- Procedure to change xmltype in clob form to object form 
SQL @install_xmltype.sql

Type 'XMLTYPE' created.

Type 'XMLSEQUENCETYPE' created.

Package 'STANDARD_EXTENSION_XML' created.

System altered.

Type 'SQLPROF_ATTR' created.

...

Package 'SYS.DBMS_METADATA' altered.

Package 'SYS.DBMS_XMLGEN' altered.

Package 'SYS.DBMS_XMLPARSER' altered.

Package 'SYS.DBMS_XSLPROCESSOR' altered.

-- Check xmltype form 
SQL @xmltype_install_checker.sql
===XMLTYPE(OBJECT) IS ALREADY INSTALLED===

===XMLTYPE(OBJECT) DEPENDENCY LIST===


===================================

PLEASE CHECK DEPENDENCY LIST.
DEPENDENCY LIST WILL BE INVALIDATED IF YOU UNINSTALL XMLTYPE(OBJECT)
YOU CAN UNINSTALL XMLTYPE(OBJECT)!!

PSM completed.

SQL conn tibero/tmax
Connected to Tibero.

SQL create table po_xml_tab (POID number, PODOC xmltype);

Table 'PO_XML_TAB' created.

SQL  insert into po_xml_tab select POID, PODOC from tmp_po_xml_tab;

1 row inserted.

SQL commit;

Commit completed.

SQL conn sys/tibero
Connected to Tibero.

SQL exec UTL_RECOMP.RECOMP_SERIAL('TIBERO'); 

PSM completed.

SQL conn tibero/tmax
Connected to Tibero.

SQL select x.podoc.getstringval() from po_xml_tab x; 

X.PODOC.GETSTRINGVAL()
---------------------------------------------------------------------------
 
       1
         Po_1
         John
         
             1033, Main Street
             Sunnyvalue
             CA
        
     


1 row selected.