5 Replies Latest reply: Jan 3, 2013 5:40 AM by odie_63 RSS

    Please advice for the correct XMLType storage

    Ronim3112
      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)
          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
            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).
            • 4. Re: Please advice for the correct XMLType storage
              615576
              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
                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