Searching through DB Table containing Records in XML format

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


0
2/10/2006 6:31:11 PM
dotnet.xml 7266 articles. 0 followers. Follow

3 Replies
814 Views

Similar Articles

[PageSpeed] 19

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" <EAGERTOLEARN@NOSPAM.NOSPAM> wrote in message 
news:eJrsDBnLGHA.2696@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
>
> 


0
2/10/2006 7:26:02 PM
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" <chris.smedley@terracomputing.com> wrote in message
news:uakGXfnLGHA.1676@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" <EAGERTOLEARN@NOSPAM.NOSPAM> wrote in message
> news:eJrsDBnLGHA.2696@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
> >
> >
>
>


0
2/10/2006 8:25:27 PM
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."

0
v-kevy (347)
2/13/2006 2:46:23 AM
Reply:

Similar Artilces:

Search last 4 digits in an (account number)
I store bank accunt and credit card numbers in various Contact Notes. For example, a credit card # as xxxxxxxxxxxx1234. Outlook contact search would not find this contact if I enter the search for "1234". Is there a way to do this in Outlook 2007? TIA PS - this search works fine in Outlook Mobile. outlook searches from the beginning - so it will only work if you use xxxxxxxxxxxx 1234. Other search tools (like google desktop) may find it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: htt...

Look for: Search In => Choose folders=> Search subfolders not working
When I search for an old email using Outlook's Look for: box and specify which folders to search in via the "Choose folders=>Search subfolders" checking a folder does not actually cause any subfolders to be checked. Unless there is some other setting and/or option hidden away somewhere, the "Search subfolders" check box is not working. I must manually check all the desired subfolders. What am I doing wrong? -- Regards, Peter Sale Santa Monica, CA USA To email me, just pull 'my-leg.' ...

Can't move tables in Relationship view
When I open Relationships in Access 2003, I can initially move just about any table. But then some of the tables become frozen at their position, and I have to wait several minutes before I can move them. What causes this? Is there a work around? I suspect the problem you describe is due to a miscommunication between Access 2003 and Windows. On Windows XP with themes, Access can get confused about what the mouse is pointing to, and doesn't recognise when you are pointing to the border of the tables in the Relationships window. This generally happens when the window has been sc...

Searching for Newsgroups
I subscribe to a Usenet service for accessing various news groups. After I download all the newsgroups from that server I can not find a way to search for the newsgroups I have interest in. When I put something like "RV" in the search box on the top right corner, it searches all of my Emails rather than just the list of groups that had "RV" in the title. Help tells me that there is a " Display newsgroups containing" box but for the life of me I can't find it. Any thoughts out there? I am running Entourage 12.2.3 Dunc Click the bar in th...

Search & replace in formulae
Is it possible to do a search and replace which includes the cell formula contents ? I have a workbook that has cells which link to the contents of a cell in another workbook and I'd like to know if the naming has to remain constant or if I can replace a part of the name periodically. ie each year. thanks "Iain Rhodes" <iain@pricejam.com> wrote in message news:8ec201c49682$858c5d20$a501280a@phx.gbl... > Is it possible to do a search and replace which includes > the cell formula contents ? I have a workbook that has > cells which link to the contents of a cel...

Should SSL Certificate Hostname=FQDN if MX record is different?
Hi, if I have MX records for "webmail.domain.com" but my Exchange host has a FQDN of "mail2.domain.com", will I have a problem with the certificate name not matching the address of the server and failing the SSL certificate authorization? Or would it be best if I add a new MX record using the FQDN and have users connect to that? Thank you, Will Hi, The certificate name must be the name that the users use when connecting from the internet. This name has nothing to do with the internal fqdn of the server and the MX record. Leif "Will Niccolls" <w...

Workflow assignment of related records to account owner
Is there a way to have related records (quotes, opportunities and orders are what I am really concerned with) automatically assigned to the account owner if someone other than the account owner creates them? I would think this would be no big deal, but I have not been able to figure it out and think I am overlooking something. Our structure pairs an inside sales person with some number of outside sales people (anywhere from 1-4). The outside sales people own all of the customer accouts. If an inside sales person creates an opportunity, for example, for a particular account, I would ...

Search engine optimization
Hello all! My question today regarding this issue is: I have noticed some sites have the following on their sites "Source". meta name= "robots" content="Index, follow" meta name= "revisit-after" content="15 days" or (30 days) What does this function accomplish as far as search engines go? and will it really imporve your results ( rankings) Like for instance "robots" "index, all" what is the difference between this and saying "robots" "index, follow" I'm a bit confused about this. Can someone ...

DoCmd to delete records
Hi I have a command button that when clicked deletes certain records from a subform based on an application number being equal. It deletes all records with the same applciation number as currently shown on the main form, see code below: DoCmd.RunSQL ("DELETE FROM tblAdultDetails WHERE [frmAdultDetails2].Form![ApplicationNum]=" + Str(Me.ApplicationNumber)) However I would like it to only delete records if the fields [MainApplicantFlag] =1 or [PartnerFlag] =1 as well as ApplicationNum being equal. These are both number fields btw. Can someone tell me what I should add to my code in...

Indexed View with table relation
Hi all I am trying to create a Index on a View. The View selects from several tables and these table have of corse relations to each other. Now I get the error: "Cannot create %S_MSG on view "%.*ls" because it references derived table "%.*ls" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view." Well, it this message true, is it not possible to create a index on a view when the underlying tables have relations ?? Thanks for any comment. Best regards Frank Uray You can...

Show all Records
I am using a combo box to filter my records in a List Box *2 problems the combo box is showing say "Transport" 6 times if I have transport in that field 6 times (No Duplicates Please) *When I select Transport it only shows 1 field, I am trying to get the list box to show all 6 fields (Show Duplicates) Combobox= Private Sub cmbFindSubject_AfterUpdate() Me!lstModify.RowSource = "SELECT * FROM qryClientInvoice Where [InvoiceID] Like " & Me.cmbFindSubject End Sub Query= SELECT tblInvoice.InvoiceID, tblInvoice.ClientDetail FROM tblInvoice WHERE (((tbl...

Can I search identical fields from multiple tables?
I have a database to log details about staff in my school. I have a tables for staff details, courses, absence, educational visits etc. In each table I have a date field to show when the member of staff is not present. I want to produce a query to search all tables by entering a date - e.g. to bring back all results for staff who are not in school on a certiain day because they are either ill, on a course or on an educational visit. The query will work by entering a date once - it will then search all the other tables and bring back the results. Is this possible? Do I restructure? ...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

relationships between tables
I have about 5 tables that I cannot enforce referential integrity on... 3 of them have a common field, but the error messages I am receiving are way above my head technically. Any suggestions? Or even documentation on relationships (from the very beginning) would help a lot! Thanks Mindy First suggestion is to post the error messages you are getting. It could be that you can't enforce referential integrity for a variety of reasons. To name two off the top. -- No unique index on field in the table on the one side -- Fields in many side already have values and there are no related val...

What does outlook search when you search Contacts ?
When you do a search in oulook for contacts, what is the criteria that outlook searches? Or I guess how does outlook search? Why is it not consistant? The reason I am asking is that we have our customers contacts in public folders. In our customers we have 6 contacts for XYZ Foods. If you search in outlook for just "XYZ" it only comes back with 4 of the six. If you do a search for "XYZ Foods" it returns with all 6. And if you just entered "Foods" no contacts were found. And we actually have a few companies in our contacts who's names are a two part name ending...

copying a cell for output-only format changes...??
I want to change the format of date data for writing out to a file, without changing the original data. How can I create a temporary copy of the current cell to do this? I have tried a couple of things but they always reference the original cell. I have a workaround, which is to save the original format, change to new format, write out date, then change back to saved format. There must be a cleaner way, but I have already floundered enough. Can someone help me? I am new to VBA (obviously...) Thanks! ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Busines...

I am having trouble keeping numbers formatted in a Pivot Table
I have a pivot table that won't stay formatted. The data is formatted as currency in the worksheet that the Pivot Table is drawing off of. It shows up as a general number in the Pivot. I've tried formatting it in the Pivot but it doesn't stay when I refresh the data. "Preserve formatting" is checked on the Pivot table, so it SHOULD be preserving any formatting I apply to the pivot table, but that doesn't seem to be happening. I know I could record a macro to format this, but it is happening on multiple Pivot tables in the same workbook and that would be a pain ...

need help parsing text to get xml
I have a string that contains the \", \t, \r, \n. I need to get the xml. sample below: "<?xml version=\"1.0\"?>\r\n<USERS xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns=\"http://www.slcorp.com\\xml\\slcorp_dtd_schema.xml\">\r\n\t<ACCT>GameTek</ACCT>\r\n\t<USER>\r\n\t\t<USER_ID>Mike</USER_ID></USER>\r\n\t</USERS>\r\n" I have tried replacing as follows so I can get the xml. I have tried 2 approaches (1) str = str.Replac...

excel VBA
How would i search a range of a whole column instead of specific cells like .Range("a1:a300")? --- Message posted from http://www.ExcelForum.com/ ..range("a:a") ..columns(1) ..range("a1").entirecolumn are a few ways. "Zygoid <" wrote: > > How would i search a range of a whole column instead of specific cells > like .Range("a1:a300")? > > --- > Message posted from http://www.ExcelForum.com/ -- Dave Peterson ec35720@msn.com ahhh! Thanks!! I was trying .Column(1) i see i neede an "s" in there Thanks a...

Command Button To Delete Text In Single Field In Record
Hello All, I want to be able to do is open a particular form, which based on a table. "Enquirys" form is called "Enquiry_Date_Reset" On the form i want to scroll to a particular record and then press a button which will clear the date which is stored in the [Date Sent] field for that particular record set. (Not the whole record) is this possible? Regards Dan On Fri, 03 Aug 2007 09:08:56 -0700, dan.cawthorne@gmail.com wrote: > Hello All, > > I want to be able to do is open a particular form, which based on a > table. "Enquirys" > > form ...

retieving data from a sql table
Hi, I'd like to be able to automatically fill certain columns in excel with data from an SQL table. For example, I have a column called "item code" and I'd like the columns "item description" and "item cost" to be automatically retrieved from an SQL table when the user inputs the item code. I'm having trouble in two areas. One, getting it to perform automatically once the data is inputed, and filtering the records to include only the description and cost for that specific item code. I've tried using macros and the "Get External Data" in ...

Joining 2 Tables
How can I join 2 tables that have the same record layouts ? On Wed, 12 Mar 2008 10:16:01 -0700, carl <carl@discussions.microsoft.com> wrote: >How can I join 2 tables that have the same record layouts ? By using an appropriate query. Since we don't know anything about the structure of the tables, how they are related (if at all), nor how you would like them "joined" it's hard to say. More info please? perhaps an example of what you have and what you want? -- John W. Vinson [MVP] Thank you for helping. Table1 is like this: Day Product Qty 2-Jan 1...

Inventory Valuation and Location Records
All: Its been awhile since I had to deploy Inventory so I need a quick refresher on Valuation Methods and their intersection with Location Codes. If I remember correctly, Average Perpetual tracks costs at the item level, with such average being applied across all sites. FIFO and LIFO (Periodic or Perpetual) all track at the Site and Item level. Is this correct? I tried to find this in the manual, but it is less than clear. Thanks, Dwight -- ...

Multiple fields using to search
Hi, Something i've been batteling for weeks with and theres probably such a simple answer to it and i just cant see it: Riiiight, i have this form (lets call it frmClientSearch) On this form i have a 2 list box thinghys; One called lstEmployee which is populated with Employee names and the other lstCity which is populated with City Names. Then i have a command button (cmdSearch) which, once click and values selected in both the lst boxes, should return only the values that are equal to both the lst boxes in a new form called frmSearchResult. (Hope i make sense) So... I know how ...

MSCRM record count
I have a SQL query that searched my products based on a product number. My goal is to see if the product exists, and set a BOOL value accordingly. My code (listed below) does work, but it seems a bit inefficient for what I want to accomplish. I’m looking for the equivalent to a COUNT statement. Any suggestions? QueryExpression query = new QueryExpression(); query.EntityName = EntityName.product.ToString(); ColumnSet productColumns = new ColumnSet(); productColumns.Attributes = new string[] { "productnumber" }; query.ColumnSet = productColumns; ConditionExpression newConditi...