Hi ramxml
Thank you for getting back to me about what version of Oracle you're
using, as this makes a considerable difference in how you might
successfully query your XML data stored in a CLOB field. Most of the
following information has been summarised from the book "Building
Oracle XML Applications" by Steve Muench, O'Reilly 2000, ISBN
1-56592-691-9. I can wholeheartedly recommend this book to solve many
of your XML/Oracle questions.
http://www.oreilly.com/catalog/orxmlapp/
As you are using Oracle 8i, then you have to associate a context with
your CLOB data if you want to be able to search its contents. This is
achieved using the Oracle interMedia text component (interMedia is the
new name for the technologies based on the old Oracle ConText
product). To quote from the O'Reilly book, p502-503:
"interMedia Text ... enables searching XML documents, document
fragments, and other document content. While the interMedia extensions
are technically a separate product, they are included on the Oracle8i
CD and can be installed and used at no additional cost." A more
detailed explanation of interMedia for Oracle 8i -- including
installation instructions -- is available at:
http://www.orafaq.com/faqctx.htm
I would therefore say that before you go any further, you must find
out whether or not you have the interMedia extensions installed, and
if not you must ensure that your DBA installs them as soon as
possible.
Once interMedia text is installed and running, you can then fairly
simply perform the searches you require (in fact, you can go much
further than you requested in your question and use XPath-like queries
to search subsections of your XML documents). The "magic bullet" which
will let you do all this is the SQL CONTAINS() function. In your case,
you would simply require the following query:
SELECT File_Name
FROM Stg_IB_Msg
WHERE CONTAINS( File_Text, '121212' ) > 0;
A couple of important points to note about this query: firstly as
stated above it will only work if you interMedia running. Secondly,
it's not necessary to add '%' wild-card markers to the text, the
CONTAINS() function searches for substrings by default. Finally note
that the CONTAINS() function allocates a "score" (of 0-100) to the
returned data, so to make any useful sense of its return value the '>
0' expression must be specified.
For the official syntax of the CONTAINS() function, and a couple of
examples, you can consult the online Oracle documentation at:
http://doc.oracle.com/cd_a87860/doc/inter.817/a77063/csql4.htm#21732
There is a lot (an AWFUL lot) more information about the interMedia
text technologies at:
http://otn.oracle.com/products/text/x/Tech_Overviews/imt_815.html
Finally, if your company and budget allows it, can I seriously
recommend you consider upgrading to Oracle 9i -- in particular 9i
Version 2 -- if you are going to be creating XML-aware applications
using Oracle. In the few short years since XML support first appeared
in the Oracle database the functionality has improved by leaps and
bounds. In 9i version 2, for example, you can run full XPath
expressions on stored XML to query or extract subsections of the XML
data, and there is no absolute requirement to have the interMedia
component running.
I hope the above enables you to continue with your development
project; if any of the above doesn't make sense to you please ask for
further clarification.
Regards
iaint-ga
Search methods:
As mentioned, much of the above knowledge came from my own personal
copy of the O'Reilly book "Building Oracle XML Applications". To find
the websites listing details of the SQL CONTAINS() function I did a
Google search for:
oracle 8i intermedia contains
://www.google.co.uk/?q=oracle+8i+intermedia+contains
oracle 8i "where contains"
://www.google.co.uk/?q=oracle+8i+%22where+contains%22
To find the syntax defintion for the CONTAINS() function I searched
within the 8i documentation at Oracle's online document repository at:
http://doc.oracle.com/ |
,
0 Comments
)