7 Replies Latest reply: Jan 18, 2011 10:12 AM by BluShadow RSS

    CLOB Vs XMLTYPE

    819404
      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
        • 1. Re: CLOB Vs XMLTYPE
          BluShadow
          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
            Billy~Verreynne
            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
              BluShadow
              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
                Billy~Verreynne
                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
                  BluShadow
                  >
                  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_63
                    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
                      BluShadow
                      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.