https://forums.oracle.com/thread/2161725

CLOB Vs XMLTYPE

819404Newbie

819404 Jan 18, 2011 9:26 AM

Hi Guys,
In one of my applications , From the front end we are getting XML data and storing the xml data in the col of table which is CLOB type .
Today one of the application developer told that directly we can store the xml data into the data base.Could any one tell me the difference in storing the data in the clob and xml
and also what is the advantages of using xml over clob data type.

Any suggestions will be highly appreciated.

Thanks,
Prafulla

 
  • 149 Views
 
  • 1. Re: CLOB Vs XMLTYPE
    BluShadowGuru Moderator

    BluShadow Jan 18, 2011 10:50 AM (in response to 819404)

    Prafulla wrote:
    Hi Guys,
    In one of my applications , From the front end we are getting XML data and storing the xml data in the col of table which is CLOB type .
    Today one of the application developer told that directly we can store the xml data into the data base.Could any one tell me the difference in storing the data in the clob and xml
    and also what is the advantages of using xml over clob data type.

    Any suggestions will be highly appreciated.

    Thanks,
    Prafulla

    XMLTYPE is based on the CLOB datatype under the hood. CLOB simply stores a whole stream of characters in one large chunk and you need to use the DBMS_LOB package to pull out any sort of structured information from that CLOB. XMLTYPE on the other hand understands that the content is XML and provides various methods for accessing the data as well as SQL being able to access the XML in a structured manner too.

    For example, if you have some XML in an XMLTYPE, you can use the XMLTABLE keyword in SQL to extract the data from it e.g..

    WITH t as (select XMLTYPE('
    <RECSET>
      <REC>
        <COUNTRY>1</COUNTRY>
        <POINT>1800</POINT>
        <USER_INFO>
          <USER_ID>1</USER_ID>
          <TARGET>28</TARGET>
          <STATE>6</STATE>
          <TASK>12</TASK>
        </USER_INFO>
        <USER_INFO>
          <USER_ID>5</USER_ID>
          <TARGET>19</TARGET>
          <STATE>1</STATE>
          <TASK>90</TASK>
        </USER_INFO>
      </REC>
      <REC>
        <COUNTRY>2</COUNTRY>
        <POINT>2400</POINT>
        <USER_INFO>
          <USER_ID>3</USER_ID>
          <TARGET>14</TARGET>
          <STATE>7</STATE>
          <TASK>5</TASK>
        </USER_INFO>
      </REC>
    </RECSET>') as xml from dual)
    -- END OF TEST DATA
    select x.country, x.point, y.user_id, y.target, y.state, y.task
    from t
        ,XMLTABLE('/RECSET/REC'
                  PASSING t.xml
                  COLUMNS country NUMBER PATH '/REC/COUNTRY'
                         ,point   NUMBER PATH '/REC/POINT'
                         ,user_info XMLTYPE PATH '/REC/*'
                 ) x
        ,XMLTABLE('/USER_INFO'
                  PASSING x.user_info
                  COLUMNS user_id NUMBER PATH '/USER_INFO/USER_ID'
                         ,target  NUMBER PATH '/USER_INFO/TARGET'
                         ,state   NUMBER PATH '/USER_INFO/STATE'
                         ,task    NUMBER PATH '/USER_INFO/TASK'
                 ) y
    
       COUNTRY      POINT    USER_ID     TARGET      STATE       TASK
    ---------- ---------- ---------- ---------- ---------- ----------
             1       1800          1         28          6         12
             1       1800          5         19          1         90
             2       2400          3         14          7          5

    It uses XQuery expressions to reference the data, so you can reference attributes of the XML elements as well as their values, and you can also use namespaces if those are needed. The above is just a simple example with some nested repeating groups.

    If you tried to extract that data using a CLOB, you’d struggle to do that in SQL easily.

  • 2. Re: CLOB Vs XMLTYPE
    BillyVerreynneOracle ACE

    BillyVerreynne Jan 18, 2011 11:08 AM (in response to 819404)

    Prafulla wrote:

    Could any one tell me the difference in storing the data in the clob and xml

    It is the difference between structured data (<i>XMLTYPE</i>) and unstructed/raw data (<i>CLOB</i>).

    If you want to use a CLOB as XML, it needs to be parsed first every time you use the CLOB. If the data is already XML, that overhead is not required.

    The usual reason for using CLOB for XML files/data is that it allows you to store the complete original XML document. Including all comments, preserving all formatting and so on.

    The XMLTYPE is a XML document object model (DOM) – the XML structure is built and all superfluous stuff in the original text document have been discarded.

    In some case, for auditing type reasons, you want to store the XML data in the actual format it was received in. In other cases, the important factor is performance and you want to parse that data into a XML DOM once only. 

    And in some cases you may want to have both and are prepared for the space and management overheads of storing both the original document as a CLOB and as a structured XML DOM object.

    So it depends on what your requirements are in this case.

  • 3. Re: CLOB Vs XMLTYPE
    BluShadowGuru Moderator

    BluShadow Jan 18, 2011 11:14 AM (in response to BillyVerreynne)

    Billy  Verreynne  wrote:
    >
    The usual reason for using CLOB for XML files/data is that it allows you to store the complete original XML document. Including all comments, preserving all formatting and so on.

    The XMLTYPE is a XML document object model (DOM) – the XML structure is built and all superfluous stuff in the original text document have been discarded.

    In some case, for auditing type reasons, you want to store the XML data in the actual format it was received in. In other cases, the important factor is performance and you want to parse that data into a XML DOM once only. 

    And in some cases you may want to have both and are prepared for the space and management overheads of storing both the original document as a CLOB and as a structured XML DOM object.

    Not forgetting that you could store the original XML on the database as a CLOB and then just convert it to an XMLTYPE for doing any processing of it simply by using XMLTYPE( clob_var ) to get the best of both. 😉

  • 4. Re: CLOB Vs XMLTYPE
    BillyVerreynneOracle ACE

    BillyVerreynne Jan 18, 2011 1:34 PM (in response to BluShadow)

    BluShadow wrote:

    Not forgetting that you could store the original XML on the database as a CLOB and then just convert it to an XMLTYPE for doing any processing of it simply by using XMLTYPE( clob_var ) to get the best of both. 😉

    Yeah – but using the XMLTYPE() function requires the CLOB to be parsed. And that is slower than not having to parse at all.

    I’ve tried to show the difference in parsing and not-parsing in the following example. Does it make sense?

    SQL> declare
      2          MAX_ITERATION   constant number := 10000;
      3          xmlData         varchar2(32767) :=
      4  '<?xml version=''1.0''?>
      5  <Root>
      6    <Element>
      7      <Name>Scott</Name>
      8      <id>1234</id>
      9    </Element>
     10  </Root>';
     11  
     12          c       clob;
     13          x       xmltype;
     14  
     15          t1      number;
     16          name    varchar2(20);
     17  
     18          procedure TimeElapsed( startTime number, processStep varchar2 ) is
     19          begin
     20                  dbms_output.put_line(
     21                          to_char( (dbms_utility.get_cpu_time-startTime)/100 )||
     22                          ' sec(s) : '||
     23                          processStep
     24                  );
     25          end;
     26  
     27  begin
     28          dbms_lob.CreateTemporary( c, true );
     29          dbms_lob.WriteAppend( c, length(xmlData), xmlData );
     30  
     31          -- parse clob as xml
     32          x := new xmltype(c);
     33  
     34          t1 := dbms_utility.get_cpu_time;
     35          for i in 1..MAX_ITERATION
     36          loop
     37                  select
     38                          ExtractValue( x, '/Root/Element/Name') into name
     39                  from    dual;
     40          end loop;
     41          TimeElapsed( t1, 'ExtractValue(xml) name='||name );
     42  
     43          t1 := dbms_utility.get_cpu_time;
     44          for i in 1..MAX_ITERATION
     45          loop
     46                  select
     47                          ExtractValue( xmltype(c), '/Root/Element/Name') into name
     48                  from    dual;
     49          end loop;
     50          TimeElapsed( t1, 'ExtractValue(clob) name='||name );
     51  
     52          dbms_lob.FreeTemporary(c);
     53  end;
     54  /
    2.55 sec(s) : ExtractValue(xml) name=Scott
    7.25 sec(s) : ExtractValue(clob) name=Scott
    
    PL/SQL procedure successfully completed.
    
    SQL> 
  • 5. Re: CLOB Vs XMLTYPE
    BluShadowGuru Moderator

    BluShadow Jan 18, 2011 1:49 PM (in response to BillyVerreynne)

    >
    Does it make sense?

    Yup, I think that clearly shows the overhead of converting from CLOB to XMLTYPE.
    Me personally, I’d store XML as XMLTYPE anyway as my work involves manipulating data, not worrying too much about comments or pretty formatting. 😉

  • 6. Re: CLOB Vs XMLTYPE
    odie_63Guru

    odie_63 Jan 18, 2011 3:51 PM (in response to BluShadow)

    BluShadow wrote:
    XMLTYPE is based on the CLOB datatype under the hood.

    Well, that’s only true for the “unstructured storage” method, which is now superseded by the BINARY XML (11g).
    And what about “structured” (aka object-relational) storage? That’s also a big part of what XMLType offers, along with XML schema support.

    Billy Verreynne wrote:
    The XMLTYPE is a XML document object model (DOM)

    Likewise, only true for unstructured storage, and still, the DOM tree is built only when needed, for example when an extract operation is requested.

    @OP (or anyone interested) : 

    Using Oracle XML DB : http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16659/xdb03usg.htm#g1055369
    Choosing Best XMLType Storage Option : http://www.oracle.com/technetwork/database/features/xmldb/xmlchoosestorage-v1-132078.pdf

  • 7. Re: CLOB Vs XMLTYPE
    BluShadowGuru Moderator

    BluShadow Jan 18, 2011 4:12 PM (in response to odie_63)

    odie_63 wrote:

    BluShadow wrote:
    XMLTYPE is based on the CLOB datatype under the hood.

    Well, that’s only true for the “unstructured storage” method, which is now superseded by the BINARY XML (11g).
    And what about “structured” (aka object-relational) storage? That’s also a big part of what XMLType offers, along with XML schema support.

    Billy Verreynne wrote:
    The XMLTYPE is a XML document object model (DOM)

    Likewise, only true for unstructured storage, and still, the DOM tree is built only when needed, for example when an extract operation is requested.

    @OP (or anyone interested) : 

    Using Oracle XML DB :http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16659/xdb03usg.htm#g1055369
    Choosing Best XMLType Storage Option :http://www.oracle.com/technetwork/database/features/xmldb/xmlchoosestorage-v1-132078.pdf

    Interesting link, that last one.

    Thanks odie.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s