Learning is not a spectator sport

Yes it’s true. People are still using XML. I know that many of us have leapt
aboard the JSON hype train, but good old XML still has its place in the IT
world. We’ve had XML in the database for decades now, and as such, we also
support it on the
Autonomous
database.

In the
documentation
for XMLTYPE, we have always offered some storage options for XML data

But the really important part here when it comes to Autonomous is the “Note”
section. Using the unstructured storage model for XMLTYPE is deprecated, and
whilst not explicitly stated, this also means that it is
unavailable on Autonomous. Attempts to use it are
blocked

SQL> create table t ( pk int, x xmltype);

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','T',user) ddl from dual;
DDL
-------------------------------------------------------------------
CREATE TABLE "ADMIN"."T"
 (    "PK" NUMBER(*,0),
      "X" "XMLTYPE"
 )  
 XMLTYPE COLUMN "X" STORE AS SECUREFILE BINARY XML  <<<=== the default

--
-- Old style no longer allowed
-- 
SQL> create table t ( pk int, x xmltype)
  2  xmltype column x store as securefile clob;
create table t ( pk int, x xmltype)
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> create table t ( pk int, x xmltype)
  2  xmltype column x store as basicfile clob;
create table t ( pk int, x xmltype)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Binary XML storage in a nutshell simply means we are at liberty to pull apart
your XML document, break it up into fragments in order to store it, manage it
and query it more efficiently. If you want to dig into the nitty gritty, you
can check this
paper
from 2007, but the key impact here is that when your retrieve your XML, it
will be reconstructed from the internal binary storage back to a valid XML
document. That document is guaranteed to align to your
original XML from the data accuracy standpoint, but it does not necessarily it
will be a byte for byte match to the XML you originally stored.

For example

SQL> 
SQL> create table t ( pk int, x xmltype);

Table created.

SQL> insert into t
  2  values (1,'<xml>        <mytag>  lots of whitspace</mytag>       </xml>');

1 row created.

SQL> select x from t;

X
-----------------------------------------------------------------------------
<xml>
<mytag>  lots of whitspace</mytag>
</xml>

SQL> select t.x.getclobval() from t t;

T.X.GETCLOBVAL()
----------------------------------------
<xml>
<mytag>  lots of whitspace</mytag>
</xml>

Notice the whitespace between tags has been removed up, but the whitespace within the data is preserved (as you’d expect).

Onto the topic at hand – I had an interesting customer requirement come in the
other day pertaining the XML on Autonomous. They had a third party application
that stored a checksum on the XML that it used, and when the XML was
retrieved, the checksum was once again calculated to ensure that the XML was
valid (from the application perspective). This application had been running in
an on-prem database for many years and now they were moving to Autonomous.

However, because of binary XML storaget on Autonomous, occasionally the XML that came back from the database was slightly different to the original source, the checksum logic would fail and the application would mistakenly mark the XML as corrupted. Of course, this can be avoided by storing the XML data is a simple CLOB, but the customer wanted to ensure that only valid XML was being stored in the database.

Here’s the solution we came up with. We would store the XML data in a CLOB to avoid any binary XML processing, but we added a check constraint to ensure that the data could be successfully converted to an XMLTYPE

SQL> create table t ( pk int, x clob not null);

Table created.

SQL> alter table t add constraint chk check ( xmltype(x) is not null );

Table altered.

SQL> insert into t
  2  values (1,'<xml> <mytag>  lots of whitspace</mytag> </xml>');

1 row created.

SQL> select x from t;

X
---------------------------------------------------------------------
<xml> <mytag>  lots of whitspace</mytag> </xml>

SQL> insert into t values (2, 'blah');

insert into t values (2, 'blah')
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00210: expected '<' instead of 'b'
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

This should do as a workaround, but ideally, if your application is dependent on byte for byte matching for an XML document, then it is perhaps missing the point on XML in the first place, namely, the metadata and tags defines the data so that you do not have to worry about the byte for byte details