This was a small mind exercise on the OakTable website (OakTable Challenge)for a person regarding how to go from a relational table to anydata datatype table and back, which I, of course, approached via an “XMLType” of way thinking. Probably the whole thing is not that practical and/or can be optimized in various ways, but as mentioned it was a small exercise. It demonstrates though, that an XMLType can easily consume and represent an Anydata datatype. The following might help people to solve some problems and/or trigger a solution.
Be-aware that some of the syntax used is deprecated and not at all guaranteed or even supported on the latest database releases…
The example can also be found on the OTN XMLDB Forum, if not only it is probably more readable on that website: “Mind exercise – Anydata and XMLType Datatypes”
--
-- Create a relational table with content
--
create table tab01
( nummer number(2)
, datum date
, string varchar2(40)
);
--
table TAB02 created.
--
-- Insert some data
--
insert into tab01 values (1, sysdate, 'a small string');
insert into tab01 values (2, sysdate+1, 'a little bit bigger string');
--
commit;
--
-- Show the output when converted to an XML document while using XMLTYPE()
--
select xmltype(cursor(select nummer, datum, string from tab01))
from dual;
--
XMLTYPE(CURSOR(SELECTNUMMER,DATUM,STRINGFROMTAB01))
---------------------------------------------------
< ?xml version="1.0"?>
1
26-10-11
a small string
2
27-10-11
a little bit bigger string
--
It is very easy to go from an XML instance format back to a relational format via the XMLTABLE function, which supports XPath V2 and XQuery V1, if needed…
--
-- Show how you can convert back from XML content into relational while using XMLTABLE
--
select t.nummer
, t.datum
, t.string
from xmltable
('/ROWSET/ROW'
PASSING xmltype(dbms_xmlgen.getxml('select * from tab01'))
COLUMNS
nummer NUMBER(2) path 'NUMMER'
, datum DATE path 'DATUM'
, string VARCHAR2(40) path 'STRING'
) t;
--
NUMMER DATUM STRING
---------------------- ------------------------- -------------------------------
1 11-10-26 a small string
2 11-10-27 a little bit bigger string
--
Now create a table with can hold an ANYDATA datatype and insert XML data based on relational table content. This has the advantage that due to Oracle’s canonical format, you know the datatypes and the format of the table.
--
-- Create a second table with an ANYDATA datatype column
--
create table tab02
(unknown anydata);
--
table TAB02 created.
--
-- Demonstrate the output of the XML content what will be inserted in the ANYDATA column
--
select xmltype(dbms_xmlgen.getxml('select * from tab01')).extract('/ROWSET').getStringVal()
from dual ;
--
XMLTYPE(DBMS_XMLGEN.GETXML('SELECT*FROMTAB01')).EXTRACT('/ROWSET').GETSTRINGVAL()
--------------------------------------------------------------------------------
1 26-10-11
a small string |
2
27-10-11 a little bit bigger string
|
It is a shame that it looks like that the “sys.anyData.ConvertClob()” method still isn’t supported… Therefore the following gave an error…
--
-- Is the method sys.anyData.ConvertClob() still not supported...?
-- A shame really, if not only that XML is almost always bigger than varchar2 in size
--
insert into tab02
(unknown)
values
(sys.anyData.ConvertClob(xmltype(dbms_xmlgen.getxml('select * from tab01')).extract('/ROWSET').getCLOBVal()) );
--
Error starting at line 67 in command:
insert into tab02
(unknown)
values
(sys.anyData.ConvertClob(xmltype(dbms_xmlgen.getxml('select * from tab01')).extract('/ROWSET').getCLOBVal()) )
Error report:
SQL Error: ORA-22370: incorrect usage of method AnyData Insert
22370. 00000 - "incorrect usage of method %s"
*Cause: This method of SYS.AnyType or SYS.AnyData or SYS.AnyDataSet is
being used inappropriately.
*Action: Check the documentation for correct usage.
So the “solution” is now limited due to the VARCHAR2 approach, but the following syntax works…
--
-- Switching back to using to ANYDATA method - sys.anyData.ConvertVarchar2()
--
insert into tab02
(unknown)
values
(sys.anyData.ConvertVarchar2(xmltype(dbms_xmlgen.getxml('select * from tab01')).extract('/ROWSET').getStringVal()) );
--
1 rows inserted.
--
commit;
--
commited.
--
-- Show sizes and anydata datatypes
--
select t.unknown.gettypeName() typeName from tab02 t;
--
TYPENAME
--------------------------
SYS.VARCHAR2
--
select vsize(unknown) from tab02;
--
VSIZE(UNKNOWN)
----------------------
291
--
To demonstrate how easy it is to convert back, while using the XMLType datatype, from AnyData back into relational data…
-- Converting back to relational - Whats now inserted in the ANYDATA column
--
select xmltype(t.unknown) content from tab02 t;
--
--
-- Original XML brackets have been replaced by lt and gt escape codes
--
CONTENT
------------------------------------------------------------
& lt;ROWSET& gt;
& lt;ROW& gt;
& lt;NUMMER& gt;1& lt;/NUMMER& gt;
& lt;DATUM& gt;26-10-11& lt;/DATUM& gt;
& lt;STRING& gt;a small string& lt;/STRING& gt;
& lt;/ROW& gt;
& lt;ROW& gt;
& lt;NUMMER& gt;2& lt;/NUMMER& gt;
& lt;DATUM& gt;27-10-11& lt;/DATUM& gt;
& lt;STRING& gt;a little bit bigger string& lt;/STRING& gt;
& lt;/ROW& gt;
& lt;/ROWSET& gt;
--
To avoid those annoying escape codes so you are able to consume the actual XML format again, you will have to use DBMS_XMLGEN, but the overload parameter doesn’t work while using SQL, so as an alternative the representative number value is used, in this case “1”…
--
-- Some possible syntax alternatives to go back to an usable XMLTYPE datatype
--
-- dbms_xmlgen.ENTITY_DECODE does NOT WORK...
--
-- select dbms_xmlgen.CONVERT(xmlserialize(content xmltype(t.unknown) as CLOB), dbms_xmlgen.ENTITY_DECODE ) content from tab02 t;
--
select dbms_xmlgen.CONVERT(xmlserialize(content xmltype(t.unknown) as CLOB), 1 ) content
from tab02 t;
--
select dbms_xmlgen.CONVERT(xmlserialize(document xmltype(t.unknown) ), 1 ) content
from tab02 t;
--
select dbms_xmlgen.CONVERT(to_clob(xmltype(t.unknown)), 1 ) content
from tab02 t;
--
-- So conversion back to normal needed
--
CONTENT
------------------------------------------------
1
26-10-11
a small string
2
27-10-11
a little bit bigger string
1
26-10-11
a small string
2
27-10-11
a little bit bigger string
1
26-10-11
a small string
2
27-10-11
a little bit bigger string
Knowing the outcome now is actually in an XML format, we are now able to do the AnyData datatype to Relational format in one go while using the XMLTABLE function.
--
-- Convert back to relational output while using the to xml converted ANYDATA content as input
--
select t.nummer
, t.datum
, t.string
from tab02 t
, xmltable
('/ANYDATA/ROWSET/ROW'
PASSING xmltype(dbms_xmlgen.CONVERT(to_clob(xmltype(t.unknown)), 1 ))
COLUMNS
nummer NUMBER(2) path 'NUMMER'
, datum DATE path 'DATUM'
, string VARCHAR2(40) path 'STRING'
) t;
--
NUMMER DATUM STRING
---------------------- ------------------------- ---------------------------
1 11-10-26 a small string
2 11-10-27 a little bit bigger string
--
As mentioned, I hope this example might help people to solve some problems and/or trigger a solution. Despite that, this example shows that it is fairly easy, via embedding an Anydata datatype in an XMLType, to show the content of the Anydata datatype.
HTH.
M.
