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
3  1342 
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**********@N OSPAM.NOSPAM> wrote in message
news:eJ******** ******@TK2MSFTN GP14.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
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******** ******@TK2MSFTN GP09.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**********@N OSPAM.NOSPAM> wrote in message
news:eJ******** ******@TK2MSFTN GP14.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
Hi Andy,
In this case, the only way is to go through all the records, read the TEXT
value into your app, parse them, and check if it meets the requirement. You
might consider to change the architecture of the database. If the search is
done so often, it might be a performance hit. Or you can consider to
migrate to SQL 2005. HTH.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: sffan |
last post by:
I am new to database programming and was curious how others solve the
problem of storing encrypted in data in db table columns and then
subsequently searching for these records.
The particular problem that I am facing is in dealing with (privacy)
critical information like credit-card #s and SSNs or business critical
information like sales opportunity size or revenue in the database. The
requirement is that this data be stored encrypted...
|
by: Geoff Jones |
last post by:
Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want to
retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and also
another 40 say numbers corresponding to the numbers in the PRODUCT field. I
want to show the rows that correspond to both these criteria.
|
by: ncgonz |
last post by:
please forgive any ignorance or illogical approaches, i am but a humble
beginner.
I have written a form (F_Auditorscorecards_Entry) for data entry that
writes to a table (T_tempAuditorScorecards). this table contains 14
records, 1 per auditor. these scorecards are to be filled out monthly,
and only once per month. the main form contains only bound controls.
attached at the bottom of the form is a subform
(F_AuditorScorecard_button)...
|
by: john |
last post by:
In my form I have a master table and a details table linked 1xM. I can
search through the whole parent table but I also like to be able to search
through the child table fields to find parent records. Should I design a new
form for this or can I somehow make this work in the same form.
Thanks in advance,
john
|
by: wvmbark |
last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months.
Every day we incur numerous service problems “Events”. Each morning we have a global conference call where events which occurred within the previous 24 hours are discussed. Prior to the call, an analyst has to review these events and provide a report, ‘The Morning...
| | |
by: Bob |
last post by:
Hi all,
I'm trying to import data, modify the data then insert it into a new
table. The code below works fine for it but it takes a really long
time for 15,000 odd records. Is there a way I can speed up the
processing substantially? as it currently takes about 10 minutes and
thats just way too long because there is many of these imports that I
need to do.... I currently insert each record one by one and I
imagine
thats where all the...
|
by: Hunk |
last post by:
Hi
I have a binary file which contains records sorted by Identifiers
which are strings. The Identifiers are stored in ascending order. I
would have to write a routine to give the record given the Identifier.
The logical way would be to read the record once and put it in an STL
container such as vector and then use lower_bound to search for a
given identifier. But for some strange reason i'm asked to not use a
container but instead...
|
by: cannonpm |
last post by:
Greetings and salutations. I have developed an A2K3 MDB and have a
search form modeled after Allen Browne's search form (http://
allenbrowne.com/ser-62.html). It works well except for records which
have multiple related records in 1:many relationships and many:to:many
relationships. To view my results, I created a query called qryAllData
which contains all of the data from several tables for each record.
For example, say I have one...
|
by: jrod11 |
last post by:
hi,
I found a jquery html table sorting code i have implemented. I am trying to figure out how to edit how many colums there are, but every time i remove code that I think controls how many colums there are, it crashes.
There are currently 6 columns, and I only want 4. How do I remove the last two (discount and date)?
Here is a link: http://www.jaredmoore.com/tablesorter/docs/salestable.html
Here is some jquery js that I think...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| | |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
