1 2 Previous Next 28 Replies Latest reply on May 1, 2010 12:26 AM by 334994

    Design for Effecient XML Content Search


      I am trying to find a solution for how to search for XML content from a web application and return results in an efficient manner. We have a table in 10G database which accepts XML data in a payload column(clob data type). We could store the data as xmltype if required or if the solution requires.

      The XML data stored in the table is in various formats. Also includes data for many entities and does not confirm to a predefined set of schemas. The data comes from various systems so the data varies in its content and type. We have almost 300-400K rows in this table so performance has to be very efficient specially since the response is being displayed in a web front end application.

      We would like to present the users with a web drop down which shows the unique tag names(from xml data in the database) and based on user input present the response based on query response.

      I would really appreciate a quick response as we have to build this solution almost immediately.

      Thanks a lot.
        • 1. Re: Design for Effecient XML Content Search
          The XMLIndex feature of 11gR1 and later provides you with EXACTLY what you are looking for. We even had a canned demo that shows how the XML Index can be used to drive a Browser based applications that shows what the given set of children are for a particular parent. Unfortunatley none of that is availalbe in 10.2.x
          • 2. Re: Design for Effecient XML Content Search
            Thanks for the prompt response. Is the 11g demo or document available somewhere that helps me look at how this is done. I doubt we can move to 11G because this will have to be a strategic organizational decision but if I can see a demo or document of this we can make a case.

            I know that its not available in 10G but given that we have 10G what is the closest alternative we can get?

            Thanks again.
            • 3. Re: Design for Effecient XML Content Search
              Marco Gralike
              It (aka "the canned" demo) was once on the XMLDB main OTN page but I can't find a link anymore. I presume Mark won't mind so I shared the "XFiles III" demo via my web page. Have a look at the corresponding pdf file. On page 30 and following you will find exactly what you are looking for / Mark was refering too. Mark's XFiles III demo application is build on the protocol server and mainly java/javascript (and a lot of cool PL/SQL examples). Anyway here are the URL's (be aware the demo is based on Oracle 11gR1 and upwards):


              Installation and other info is contained in the pdf.


              • 4. Re: Design for Effecient XML Content Search
                Thanks for the details. I'm locally installing 11G and will try to run the app. locally and see how it works out.

                In the meanwhile since we do not have 11G in our environment any ideas on the closest solution in 10G would help.

                • 5. Re: Design for Effecient XML Content Search
                  Marco Gralike
                  Using an Oracle Text index
                  • 6. Re: Design for Effecient XML Content Search
                    Could you provide some examples of using Oracle text index in 10G? I have never worked on indexes in oracle.

                    • 7. Re: Design for Effecient XML Content Search
                      Marco Gralike
                      There is a simple example in the Oracle XMLDB Development Guide and a lot more info in the Oracle Text manuals...
                      • 8. Re: Design for Effecient XML Content Search
                        I have tried to install XFiles application but the application does not seem to start. I modified my local config.bat file to provide local connection parameters and then ran the install.bat file which ran many sql's and then finished.

                        I then tried to go to http://localhost/xfiles but get a broken link.

                        My config.bat is given below.

                        How can I verify if the installation is successful or what the issues are ? There are a bunch of log files created under the XFilesIII dir as a result of running the install.bat but I'm not certain which one will have the issue causing the application to not work. Also assuming we can find out the issue at hand, can run the reinstall.bat and rerun the install if needed or do I need to back out the install in some way and then reinstall using the reinstall.bat file?

                        Btw, my localhost port 80 is not being used by any other application which is why I set the ORAHTTPPORT=80


                        set HOSTNAME=myPC
                        set ORAHTTPPORT=80
                        set USERTBLSPACE=xmltest
                        set TEMPTBLSPACE=TEMP01
                        set ORASQLUSER=XFILES
                        set ORASQLPASSWORD=xfiles
                        set ORASYSPASSWORD=abcde
                        set TNSALIAS=mylocaloraclesid
                        set REMOTE=FALSE

                        • 9. Re: Design for Effecient XML Content Search
                          Marco Gralike
                          Are you on Windows or on Linux/Unix ?
                          Does http://localhost work as is ?
                          Do you have any invalid objects in the database ?
                          Which database version are you using with XFILES III ?
                          • 10. Re: Design for Effecient XML Content Search
                            I'm on Windows 7

                            http://localhost does not work because I have nothing else(no web server) running on this pc for localhost to work by itself. Its a pretty new pc.

                            I'm using a local Oracle 11G installed on the pc. I installed Oracle 11G only for this and have not installed any thing beyond setting up a local database instance in it. The database did not have anything in it before I ran the install.bat file.

                            Do you want me to connect to the local db and verify if there are any objects which show up as invalid or how do you want me to verify this?

                            • 11. Re: Design for Effecient XML Content Search
                              Marco Gralike
                              I will get my Windows 7 based new laptop in 1-2 weeks time so can't check or reproduce.

                              IF you address on AN operating system (linux/windows etc) and use ports below portnumber 1024, than you will have to be local administrator / root etc. You are not allowed to claim a port on port 80 if your not a superuser. Due to the the tightened security in Windows Vista/Windows 7 and Windows 2008 you need to be fully privileged, a "look-a-like" local admin is not good enough anymore.

                              That's why I asked you to check if http://localhost(:80) works. That would mean that the XFILES scripting at least set the port on port 80 and enabled the XDB Protocol Server to accept HTTP calls on port 80. Before you start you will have to have a database with XMLDB installed (see also http://www.liberidu.com/blog/?p=1214). Of course a demo database install would also suffice.
                              • 12. Re: Design for Effecient XML Content Search
                                I just have 1 admin account(infact just one account) on the laptop which is used for everything so I would assume that should suffice. Should it not? When I installed 11G I also installed XMLDB as part of the installation so that is already installed.

                                Can I try to reset the port to more than 1024 and reinstall using the reinstall.bat file?

                                • 13. Re: Design for Effecient XML Content Search
                                  Marco Gralike
                                  Step 1.

                                  Does http://localhost work?

                                  Step 2.

                                  (if not) then

                                  Login via SQL*Plus using the sys account
                                  SQL> conn / as sysdba
                                  SQL> -- get current XDB Protocol server status (port 0 = disabled, all others = enabled
                                  SQL> select dbms_xdb.getHttpPort() from dual;
                                  SQL> -- set http port on port 80 or default 8080
                                  SQL> execute dbms_xdb.setHttpPort(80);
                                  SQL> -- check status via 
                                  SQL> select dbms_xdb.getHttpPort() from dual;
                                  SQL> host lsnrctl status
                                  Step 3.

                                  Check if it works via http://localhost or http://localhost:80 or http://localhost:8080 etc. Default port in HTTP, so is not explicitly set is port 80, just as port 443 is when using "httpS"

                                  • 14. Re: Design for Effecient XML Content Search
                                    I had forgotten that I had wordpress running on port 80. I changed the port for this app to 8080 and the app loads up now. Thanks for your help. I will go through the app and try to execute the xml content searches I want to test and let you know if I have any more questions.

                                    1 2 Previous Next