Chris,
Thank you very much for your time and response. At this point I cannot
migrate to SQL Server 2005 as my environment and infrastructure (Dev /
Staging and Production Servers numbering more than 15) have to be upgraded.
Also there are other applications which have their database on the same box
which will have to be tested before the upgrade (my company protocol demands
that ).
Team/Guru's
So I am still awaiting for any ideas or suggestions. Please advise.
Best
-A
"Chris Smedley" <ch***********@terracomputing.com> wrote in message
news:ua**************@TK2MSFTNGP09.phx.gbl...
I know you mention you are using SQL 2000.... have you looked at the
features within SQL2005... there are serious improvements in the ability
to manipulate and search XML documents using XPath...you can query within an
XML field.. which looks like what you want...I only mention this so that
you might balance your immediate needs against development time.
You can also get SQL 2005 Express for free.
"ANDY AIYER" <EA**********@NOSPAM.NOSPAM> wrote in message
news:eJ**************@TK2MSFTNGP14.phx.gbl... Guru's!
Your time and guidance is much appreciated in this task that i am trying
to
get done.
Background
I have a SQL Server 2000 database table which contains 2 Fields
(RecordID, XMLData (datatype=TEXT). There are about 10,000 records in this table.
The Data in the field (XMLData) is a XML formatted string. (where F1,F2...
are elements and v1, v2 is the data)
<ControlRecord><F1>V1</F1><F2>V2</F2><F3>V3</F3><F4>V4</F4><F5>v5</F5><f6>v6 </f6>.....Upto 14 fields </ControlRecord>
<ControlRecord><F1>V1</F1><F2>V2</F2><F3>V3</F3><F4>V4</F4><F5>v5</F5><f6>v6
</f6></ControlRecord>
and so on.....
Now, i have to create a search application in ASP.NET which will allow
users
to search (and retrieve content) this data based on whats there in each
field (F1, F2, F3) The interface is a query page containing a data entry
screen to type in search values for each of the Fields.
Each search will have to look through all the 10K records, i was
wondering what would be the best approach? Do i need to load all the data into one
huge XML doc and then search (if so how)? or should i forget XML and
simply
search using the like operator (Example Where F1 like '%<f1>[value to be
searched]</f1>%' (i am pretty sure this method is going to crash).
A simple example on the best approach on how to load and search for data
in
2 elemetnts would be great
I understand what i may be asking may be horribly 'basic stuff' but I
have had limitted XML experience before and hoping someone would help! I am
having concerns over trying to load a 1000 rec data and searching...
Thanks
-Andy