Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Nikhil.

Asked: October 03, 2016 - 3:56 am UTC

Answered by: Connor McDonald - Last updated: December 13, 2019 - 3:27 am UTC

Category: Database - Version: 11 g

Viewed 10K+ times! This question is

You Asked

Hello Team,

While doing poc for storing XML in ClOB storage and Binary XML storage ,I could see storing XML in Binary XML takes less table space as compared to CLOB .As far as I know both store XML in LOB storage.so why there is difference between space taken Binary XML storage is less than XML CLOB storage .

Do oracle remove white spaces while storing XML in Binary XML storage ? Or it modifies XML to take less space ?

Can you explain how storing XML in Binary XML works ? and what is difference between CLOB storage and Binary XML storage .

Thanks

and we said...

If you want the low level details, here's a document on it:

http://www.oracle.com/technetwork/database-features/xmldb/oracle-binaryxml-rfc-128974.pdf

But perhaps its sufficient to take what is in the documentation:

"Binary XML storage (the default) – XMLType data is stored in a post-parse, binary format designed specifically for XML data. Binary XML is compact, post-parse, XML schema-aware XML data. This is also referred to as post-parse persistence."

Worthy of note:

"Starting with Oracle Database 12c Release 1 (12.1.0.1), the unstructured (CLOB) storage model for XMLType is deprecated. Use binary XML storage instead."


and you rated our response

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Document Fidelity maintain in Binary XML?

October 04, 2016 - 1:49 pm UTC

Reviewer: Nikhil from India

Thanks for your quick response

Just going through the document given on link

http://www.liberidu.com/blog/2007/06/24/oracle-11g-xmltype-storage-options/

XMLType based CLOB storage is ideal for situations were you have to process document centric XML data, for instance, you don’t want the XML document altered in anyway and you store or retrieve always the whole document. The document keeps intact and no “shredding” takes place.

As currently we want that our XML content should not be changed .
So in case of Binary XML storage document fidelity is maintained?

Currently our table structure is like

CREATE TABLE clob_table (x SYS.XMLTYPE,y VARCHAR2(30)) XMLTYPE x STORE AS BASICFILE CLOB;

and we are planning to move to new table

CREATE TABLE binaryxml_table (x XMLTYPE,y VARCHAR2(30)) XMLTYPE x STORE AS BINARY XML;


Connor McDonald

Followup  

October 05, 2016 - 1:07 am UTC

I *think* that post is referring to the difference between clob and the shredding into object-relational views. My understanding is that binary XML and clob will basically be synonymous.

I'm seeking clarification from the product manager on that.

Clob vs Binary XML

October 05, 2016 - 6:05 am UTC

Reviewer: Nikhil Mehrotra

Thanks for your reply.
We are waiting for the clarification from your product manager .

Findings : Clob vs Binary XML

October 05, 2016 - 7:08 am UTC

Reviewer: Nikhil Mehrotra

Just an update ,I have compared the HEX value of both xml stored in BasicFile CLOB and Binary XML and they seems to be different ,
It means document fidelity is not maintained when we store XML in Binary XML.
Connor McDonald

Followup  

October 06, 2016 - 12:47 am UTC

Can you provide a test case please - so I can take it further

April 25, 2019 - 9:44 pm UTC

Reviewer: A reader


April 25, 2019 - 9:45 pm UTC

Reviewer: April from NJ USA


Example of difference between CLOB and BINARY

November 13, 2019 - 10:09 pm UTC

Reviewer: Dan T from Boise, ID USA

Here is an example of where saving XMLTYPE as BINARY or CLOB will have different results in the XML:

SELECT xmltype (q'|<variable name="NL" select="'XX|' || CHR (38) || q'|#10;XX'" />|') xml_doc FROM DUAL;

SELECT xmltype (q'|<variable name="NL" select="'XX
XX'" />|') xml_doc FROM DUAL;

CREATE TABLE xml_bin
(
   xml_id    NUMBER,
   xml_doc   SYS.XMLTYPE
)
XMLTYPE xml_doc STORE AS BINARY XML;

INSERT INTO xml_bin (xml_id, xml_doc)
     VALUES (1, xmltype (q'|<variable name="NL" select="'XX|' || CHR (38) || q'|#10;XX'" />|'));

INSERT INTO xml_bin (xml_id, xml_doc)
     VALUES (2, xmltype (q'|<variable name="NL" select="'XX
XX'" />|'         ));

SELECT xml_id, xml_doc xml_doc FROM xml_bin; -- Newlines in the "select" are removed

CREATE TABLE xml_clob
(
   xml_id    NUMBER,
   xml_doc   SYS.XMLTYPE
)
XMLTYPE xml_doc STORE AS CLOB;

INSERT INTO xml_clob (xml_id, xml_doc)
     VALUES (1, xmltype (q'|<variable name="NL" select="'XX|' || CHR (38) || q'|#10;XX'" />|'));

INSERT INTO xml_clob (xml_id, xml_doc)
     VALUES (2, xmltype (q'|<variable name="NL" select="'XX
XX'" />|'         ));

SELECT xml_id, xml_doc xml_doc FROM xml_clob; -- Newlines in the "select" are retained

Connor McDonald

Followup  

December 13, 2019 - 3:27 am UTC

Hmmm....

SQL> CREATE TABLE xml_bin
  2  (
  3     xml_id    NUMBER,
  4     xml_doc   SYS.XMLTYPE
  5  )
  6  XMLTYPE xml_doc STORE AS BINARY XML;

Table created.

SQL>
SQL> begin
  2  INSERT INTO xml_bin (xml_id, xml_doc)
  3       VALUES (1, xmltype (q'|<variable name="NL" select="'XX|' || CHR (38) || q'|#10;XX'" />|'));
  4
  5  INSERT INTO xml_bin (xml_id, xml_doc)
  6       VALUES (2, xmltype (q'|<variable name="NL" select="'XX
  7  XX'" />|'         ));
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT xml_id, xml_doc xml_doc FROM xml_bin;

    XML_ID XML_DOC
---------- ----------------------------------------------------------------------------------------------------
         1 <variable name="NL" select="&apos;XX&#xA;XX&apos;"/>
         2 <variable name="NL" select="&apos;XX XX&apos;"/>

2 rows selected.

SQL>
SQL> CREATE TABLE xml_clob
  2  (
  3     xml_id    NUMBER,
  4     xml_doc   SYS.XMLTYPE
  5  )
  6  XMLTYPE xml_doc STORE AS CLOB;

Table created.

SQL>
SQL> begin
  2  INSERT INTO xml_clob (xml_id, xml_doc)
  3       VALUES (1, xmltype (q'|<variable name="NL" select="'XX|' || CHR (38) || q'|#10;XX'" />|'));
  4
  5  INSERT INTO xml_clob (xml_id, xml_doc)
  6       VALUES (2, xmltype (q'|<variable name="NL" select="'XX
  7  XX'" />|'         ));
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT xml_id, xml_doc xml_doc FROM xml_clob;

    XML_ID XML_DOC
---------- ----------------------------------------------------------------------------------------------------
         1 <variable name="NL" select="&apos;XX&#xA;XX&apos;"/>
         2 <variable name="NL" select="&apos;XX XX&apos;"/>

2 rows selected.