This discussion is archived
5 Replies Latest reply: Jan 3, 2013 3:40 AM by odie_63 RSS

Please advice for the correct XMLType storage

Ronim3112 Newbie
Currently Being Moderated
In my application there is a major column having LONG data type holding XMLs like the one below.
I wish to transfer it into an XMLType, but I am not sure which XMLType storage is the right one (Binary, Structured, Hybrid).
Users are making a lot of UPDATES and a lot of SELECT queries during the day.
Also, I would like to have FULL TEXT SEARCH on those XMLs.
And of course I need to have a very good indexing abilities in order to retrieve queries results fast.
The XMLs are not all of the same schema, in fact today we do not use xsd at all,
but it is no problem creating such schemas, those schemas will not be change frequently.

This is my ORACLE versiom:
BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

XML sample:
<ITEM>
    <REMARKS_ABOVE3><![CDATA[]]></REMARKS_ABOVE3>
    <REMARKS_ABOVE2><![CDATA[<FONT size=2><STRONG><FONT color=darkblue>simv lb:<BR/></FONT>ain ladcn at mvzri hhtciibvt</STRONG>, hmvzrim mtadcnim bavpn avtvmti tvh 48 savt.</FONT>]]></REMARKS_ABOVE2>
    <APPENDIX>
        <RECORD id="0,0,0,0" order="1,2,3,4" >
            <FROM_DATE_APP day="01,01,01,01" month="04,04,04,04" year="2012,2012,2012,2012">01/04/2012,01/04/2012,01/04/2012,01/04/2012</FROM_DATE_APP>
            <FILE1 new_window="on,on,on,on">cortalpdf/JK1270311_01042012_1.PDF,cortalpdf/JK1270311_01042012_1.PDF,cortalpdf/JK1270311_01042012_1.PDF,cortalpdf/JK1270311_01042012_1.PDF</FILE1>
            <FILE><![CDATA[]]></FILE>
        </RECORD>
    </APPENDIX>
    <PROVIDER></PROVIDER>
    <PACKAGE_TABLE values="1,18,2,5,20,3,7,4,17,8,19,6,9,11,10">
        <VALUE >10</VALUE>
        <VALUE >11</VALUE>
        <VALUE >9</VALUE>
        <VALUE >6</VALUE>
        <VALUE >19</VALUE>
        <VALUE >8</VALUE>
        <VALUE >17</VALUE>
        <VALUE >4</VALUE>
        <VALUE >7</VALUE>
        <VALUE >3</VALUE>
        <VALUE >20</VALUE>
        <VALUE >5</VALUE>
        <VALUE >2</VALUE>
        <VALUE >18</VALUE>
        <VALUE >1</VALUE>
    </PACKAGE_TABLE>
    <FOLDER_COLOR></FOLDER_COLOR>
    <RESTRICT>
        <BUTTON >
            <NAME>Bookmark</NAME>
        </BUTTON>
        <BUTTON >
            <NAME>Print</NAME>
        </BUTTON>
        <BUTTON >
            <NAME>Email</NAME>
        </BUTTON>
        <BUTTON >
            <NAME>Fax</NAME>
        </BUTTON>
        <BUTTON >
            <NAME>Feedback</NAME>
        </BUTTON>
    </RESTRICT>
    <PLUG_POINT__INCLUDED></PLUG_POINT__INCLUDED>
    <PAYMENT_TERMS values="4">
        <VALUE >4</VALUE>
    </PAYMENT_TERMS>
    <REMARKS_DOWN3><![CDATA[]]></REMARKS_DOWN3>
    <REMARKS_DOWN2><![CDATA[]]></REMARKS_DOWN2>
    <AUDIENCE><![CDATA[<FONT size=2>lkvc kiim hmvsip yes MaxTOTAL</FONT>]]></AUDIENCE>
    <CONTENT_CODE>NIL</CONTENT_CODE>
    <OFFER_ID>999400</OFFER_ID>
    <OPERATION_PAGE new_window="on">
        <URL>http://pegasus:7777/apex/f?p=104:9:::::P9_OFFER_TEST:168228</URL>
        <CAPTION>168228</CAPTION>
    </OPERATION_PAGE>
    <FROM_DATE day="15" month="03" year="2012">15/03/2012</FROM_DATE>
    <WIZARD_CODE>168228</WIZARD_CODE>
    <CAMPAIGN_TYPE values="3">
        <VALUE >3</VALUE>
    </CAMPAIGN_TYPE>
    <REMARKS_AND_MORE><![CDATA[]]></REMARKS_AND_MORE>
    <END_CAMPAIGN_PERIOD><![CDATA[<FONT face="Ariel" size=2><A href="javascript:getInlineObject(102563)">ldgsim btvm htkvph lczv can</A></FONT>]]></END_CAMPAIGN_PERIOD>
    <CONVERTER_INCLUDED>34</CONVERTER_INCLUDED>
    <LEGAL_NOTE><![CDATA[]]></LEGAL_NOTE>
    <CAMPAIGN_ENDERS><![CDATA[]]></CAMPAIGN_ENDERS>
    <SERVICE_CENTER values="44">
        <VALUE >44</VALUE>
    </SERVICE_CENTER>
    <REMARKS9><![CDATA[]]></REMARKS9>
    <REMARKS8><![CDATA[]]></REMARKS8>
    <PERIOD>
        <TEXT order="3" >lhvdah cdsh</TEXT>
        <TEXT order="2" > vad </TEXT>
        <TEXT order="1" >hcl m-</TEXT>
    </PERIOD>
    <REMARKS7><![CDATA[<P><FONT size=2><A href="javascript:getInlineObject(17177)">rav bcirt arvzi primivm tct piltr PRICE LIST</A></FONT></P>]]></REMARKS7>
    <SPEED></SPEED>
    <CAMPAIGN_PERIOD_EMPHSIS><![CDATA[<P><TABLE dir=rtl style="WIDTH: 100%" cellSpacing=1 cellPadding=1 width="100%" border=0><TBODY><TR vAlign=center><TD class=blank style="BACKGROUND-COLOR: #e0f0fe" vAlign=center><FONT color=#00008b><STRONG> <IMG alt=""" hspace=0 src="http://infweb02/archive/externalFiles/Icons/BulletsFull.gif" align=baseline border=0> <FONT color=#1c1c95>dgsim ltkvpt hmbza:</FONT></STRONG></FONT></TD></TR><TR><TD class=blank style="BACKGROUND-COLOR: white" vAlign=center><TABLE dir=rtl cellSpacing=1 cellPadding=1 width="100%" border=0><TBODY><TR><TD class=blank>lavr kizvr tkvpt hmbzaim, is lbdvk at tkvpt hmbza sl hlkvc lpi hmvadcn bmarct simpl.</TD></TR></TBODY></TABLE></TD></TR><TR vAlign=center><TD class=blank style="BACKGROUND-COLOR: #e0f0fe" vAlign=center><STRONG><FONT color=#00008b><P><STRONG><FONT color=darkblue size=2> <IMG alt=""" hspace=0 src="http://infweb02/archive/externalFiles/Icons/BulletsFull.gif" align=baseline border=0> <FONT color=#1c1c95>dgsim lmtn hmbza:</FONT></FONT></STRONG></P></FONT></STRONG></TD></TR><TR vAlign=center><TD class=blank style="BACKGROUND-COLOR: white" vAlign=center>ldgsim lmtn srti DVDbox / Max Movies <A href="javascript:getInlineObject(532088)">lczv can</A>.</TD></TR></TBODY></TABLE></P>]]></CAMPAIGN_PERIOD_EMPHSIS>
    <INT_CAMPAIGN_TYPE></INT_CAMPAIGN_TYPE>
    <INCLUDE_INTERNET>NO</INCLUDE_INTERNET>
    <WORLDS values="7">
        <VALUE >7</VALUE>
    </WORLDS>
    <CAMPAIGN_PERIOD>13</CAMPAIGN_PERIOD>
</ITEM>
  • 1. Re: Please advice for the correct XMLType storage
    Jason_(A_Non) Expert
    Currently Being Moderated
    Have you read the Oracle whitepaper "Oracle XML DB : Choosing the Best XMLType Storage Option for Your Use Case (PDF) ", which can be found at the [url http://www.oracle.com/technetwork/database/features/xmldb/index.html]Oracle XML DB Home.

    If you want all the XML in one column, then Binary would be the best option for you. Make sure you either upgrade to .3 or specify SECUREFILE BINARY when creating the XMLType column, as that became the default value in .2

    If you are wanting to put the XML in different columns/tables based on the XML schema it is associated with, then Structured may be want you want. The above mentioned document will help in your decision.
  • 2. Re: Please advice for the correct XMLType storage
    Ronim3112 Newbie
    Currently Being Moderated
    Thank you for your answer.
    Yes I did read the document.
    What do you mean in "upgrade to 3".
    And should I use structured XML index or text index (I need full text search).
  • 3. Re: Please advice for the correct XMLType storage
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    11.2.0.*3*.0
  • 4. Re: Please advice for the correct XMLType storage
    615576 Oracle ACE
    Currently Being Moderated
    Hi, i shared the comment, i recommend binary xml, you could do the upgrade to 11.2.0.2 or higher, but also can explicit put that the column will be a binary xml.
    If you will work with schema (xsd) remember also register for a binary xml.

    Regards.
    Francisco.
  • 5. Re: Please advice for the correct XMLType storage
    odie_63 Guru
    Currently Being Moderated
    The flowchart says you should use binary XML storage with an XMLIndex (structured + unstructured) and a Text index if necessary.
    Users are making a lot of UPDATES and a lot of SELECT queries during the day.
    Are they piecewise updates or updates of the whole documents?

    On 11.2.0.3 you can use XQuery Update Facility to perform complex piecewise updates in a single operation :
    SQL> update my_table t
      2  set t.my_xml =
      3      xmlquery(
      4        'copy $d := .
      5         modify (
      6           let $p := $d/ITEM/PACKAGE_TABLE
      7           return (
      8             replace value of node $p/@values with concat($v, ",", $p/@values)
      9           , insert node element VALUE {$v} as first into $p
     10           )
     11         )
     12         return $d'
     13        passing t.my_xml
     14              , 99 as "v"
     15        returning content
     16      )
     17  where t.id = 1 ;
     
    1 row updated
     
    SQL> 
    SQL> select x.vals as "values"
      2  from my_table t
      3     , xmltable('/ITEM/PACKAGE_TABLE'
      4         passing t.my_xml
      5         columns vals varchar2(80) path '@values'
      6       ) x
      7  ;
     
    values
    --------------------------------------------------------------------------------
    99,1,18,2,5,20,3,7,4,17,8,19,6,9,11,10
     
    SQL> 
    SQL> select x.val as "VALUE"
      2  from my_table t
      3     , xmltable('/ITEM/PACKAGE_TABLE/VALUE'
      4         passing t.my_xml
      5         columns val number path '.'
      6       ) x
      7  ;
     
         VALUE
    ----------
            99
            10
            11
             9
             6
            19
             8
            17
             4
             7
             3
            20
             5
             2
            18
             1
     
    16 rows selected
     
    Also, I would like to have FULL TEXT SEARCH on those XMLs.
    XQuery Full Text as proposed by W3C will be available on 12c.

    For the latest releases, you can use a Text index or the ora:contains XQuery function :
    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb09sea.htm#i1006756

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points