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
1538 Views

Similar Articles

[PageSpeed] 56

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:

Formatting XmlSerializer Output
I'm using classes generated from xsd.exe. I am serializing them with XmlTextWriter, and everything works fine. Our customer, though, has a silly requirement that dollar amounts (in the .xsd as decimals) be formatted to always include cents (regardless if there are cents or not). I'm interested in using the classes to only output data (i.e. I'm not parsing incoming data) I've tried setting up the following construct to format the output of each of the affected decimal fields, but I don't get anything written out to the .xml file. What am I missing - and is there a bette...

XmlReader and XML Web Control
Hi I am trying to use the XML Web Control and the XmlReade but when I try and load the xmlReader into the XmlDocument is gives me this erro Error: This document already has a DocumentElement node It fails at: doc.Load(myXmlReader Is there something I am missing Here is my code Dim myCommand As SqlCommand = New SqlCommand("Select proddesc from invli where custid = '1STRFA' for xml auto", myConnection myConnection.Open( Dim myXmlReader As System.Xml.XmlReader = myCommand.ExecuteXmlReader( Dim doc As XmlDocument = New XmlDocumen doc.Load(myXmlReader Dim trans As XslTrans...

Formatting unlocked cells
How to do formatting of unlocked cells? I have locked certain cells and wish the user to have freedom o formatting other (unlocked) cells. But once the sheet is protected formatting etc. are not possible even for unlocked cells. I am using Excel2000 and under protection password dialogue, the chec boxes are as 'Contents', 'Objects' & 'Scenarios'. How to solve the problem -- Message posted from http://www.ExcelForum.com Hi Bhuktar You can't, this behaviour is by design in Excel. HTH Cordially Pascal "Bhuktar S >" <<Bhuktar.S.18lx28@excelfo...

Display Cond. Format Formulas
Is there a way to display a cell's conditional format formula w/o actually going through the menu to look at it? I need a way to quickly tell what formula a cell is using for conditional formatting. It would be nice if you could hover your mouse over the cell and the formula would be diplayed (similar to a comment), but I don't know if this is available. Thanks. >>It would be nice...but I don't know if this is available It's not. About all you can do is speed up the process of opening the dialog slightly with a macro. You could attach this macro to a toolbutto...

Manipulating field values in a form record
Hello, As a user enters certain values into fields in my data entry form I would like to give the user the option of clicking on a command button that triggers a calculation that would then fill in several other fields in the same form record. It seems to me that I would need to write a VB procedure to do this, so what I would need would be code examples of how form fields are accessed and assigned values in a VB procedure. I can handle the rest of the VB coding once I know how to do that, Regards, David Allison -- David T. Allison David, A few examples would have been helpf...

vbScript a search of a PST
Hello, I'm looking for some vbScript help.. I need to automate searching a PST file to emails containing key words... Then I need to export those messages matching the search criteria to a fresh PST... Anyway to do this?? Troy "Troy Bruder" <troy_bruder@hotmail.com> wrote in message news:eK6wDIIRFHA.1348@TK2MSFTNGP15.phx.gbl... > Hello, > > I'm looking for some vbScript help.. I need to automate searching a PST > file to emails containing key words... Then I need to export those messages > matching the search criteria to a fresh PST... > &...

CRM Beta 3.0
OK in Outlook, perhaps I have missed something. You can open a single record and then from the Actions menu, choose Assign. But what if I want to choose multiple Accounts, then assign them to someone else. When I select multiple items, the More Actions menu only shows Sharing... no Assign. If I have to reassign a larger number of contacts say 15 - 20, do I have to do them one at a time or is there a fast multi-select way to so this re-assignment? Thanks! Shauna Create a manual workflow rule that Assigns them to whoever you want, select the accounts you want to assign and choose Appl...

Time Field in transaction table
I am trying to build some custom queries to fill spreadsheets with some data and I am running into a weird problem. The transactions times are quite a few hours ahead. For example, I know for certain some of the transactions happened on 5/9/2009, and the sales report in POS shows the transaction showing up on 5/29/2009, but when I look at the raw data in the table, the transaction time column shows 5/10/2009. Is the time setup as so many hours from GMT or something that I am not accounting for? I assume it just went by system time of the computer, but so far its not looking that way....

AllowAdditions updates record?
Greetings:I have a form that opens with AllowAdditions set to false. When theuser clicks the Add command button, I set AllowAdditions to true, moveto a new record, and set AllowAdditions back to false. Trouble is,setting AllowAdditions to false apparently causes Access to try tosave the new record, which in turn throws a key violation errorbecause the user hasn't entered any data yet.1. Am I correct in assuming the changing the AllowAdditions propertytriggers an update?2. Is there a way to disallow additions without Access trying to savethe new record?TIAKeith > 1. Am I correct in assum...

Two Tables using one Enquiry Table?
I have three tables called tblStudents, tblTeachers and tblEnquiries. tblStudents = StudentID, DOB, Address, etc... tblTeachers = TeacherID, DOB, Address, etc... If I just wanted an Enquiries table for Students, creating the fields would be easy: tblEnquiries = EnqDate, StudentID, EnqDescription, EnqFollowupDate Likewise if I just wanted an Enquiries table for Teachers, creating the fields would be easy: tblEnquiries = EnqDate, TeacherID, EnqDescription, EnqFollowupDate But I need a single enquiry table to manage enquiries from both students and teachers. How do I do this? Thanks, ...

MFC/DAO app I have to limit records in one table to 5,000 of the newest?
Hello, I know this is a DB question but I'll give it a shot in the MFC group. I have a MFC/DAO app I have to limit records in one table to 5,000 of the newest. Is it ok to Query the "PrimaryKey" to delete the oldest records? I'm using the Primary Key "Auto Number" to find the oldest records. I set the "m_strSort" to the Primary Key field call Requrey() and delete the lowest number entries so the records are limited to 5,000 of the newest. If I do a "Compact Database" the order is sill preserved because only the tail end is deleted. Is it ok...

Show Alll records Types relating to PersonID
I'm Trying to create a charge report from a tbl called tblcharges which has the following ClaimID PersonID DOSFrom DOsTo Procedure Fee BillTo which is a combo box and has a macro event which is a requery and the following sql code In this combo box patient could have 2 insurances example Medicare which is Primary And Medical which Is Secondary and this will be stored in the tblPeopleInsurance.Type Field which are P = Primary and S= Secondary. Lets Say I will select Medicare because this is the patient primary insurance. SELECT tblPeopleInsurance.GuaranterID, tblPeopleInsurance.PersonID, ...

xml as a new layer
Hi, Currently I have 3 layers in my application (client,business logics,server). I want to use xml layer in between client and server, I want some examples in which data must pass through from my Editable Grid to XML and from XML file to my Server. what is the real need to use XML in this scenario? Any help is very much appreciated. Thanks in advance Regards CT ...

Pivot table
Hi guys, Just wondering whether this is possible. I would like to pivot a group of data in which for example it has cost centres in column "A" then Projects in column "B". Column C onwards consisted of each months. The question is can I pivot the data in which it will first be by Cost Centres then projects, then months by each column? I know I can achieve having the months in each column by using the multiple consolidations ranges in the pivot wizard, but after the data has been pivoted, it doesn't allow be to separate the cost centres and projects like I can do ...

xml schema import help
Hi I'm stuck with the following schema validation problem in VS.NET 2003: I have two types of xml document and related schema: project and projectCollection. A projectcollection is just a set of projects. Therefore, I wish to include the project customType in the projectCollection namespace. I therefore have declared two xsd documents: project.xsd and projectcollection.xsd These both validate. and two xml documents to test validation: project.xml and projectcollection.xml. project.xml validates perfectly, however all of the project tags within projectcollection.xml are not validated. Can ...

Retrieving the most recent record from each person
I have a table of scores and I'd like to obtain the most recent record for each person, but not everyone has an entry for a particular day. For example, if I have three users with the following scores, entered on the following days: Name1 10 1/1/08 Name2 20 1/1/08 Name3 30 1/1/08 Name1 11 1/8/08 Name3 31 1/8/08 I would expect to get Name1 11 1/8/08 Name2 20 1/1/08 Name3 31 1/8/08 I've tried several queries, but I only get the names for the most recent date, skipping anyone without a record for 1/8/08. This is a result of the TOP 1 clause. Without this, though...

XML namespaces and XSD.exe
Oh boy i'm getting tangled in knots here. I have a schema which contains <xs:schema targetNamespace="http://Southend.Schemas.PAS/PASPatientDemographics.xsd" id="PASPatientDetails" xmlns:sh="http://Southend.Schemas.PAS/PASPatientDemographics.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> now i used the xsd.exe and it now generates elements when I serilaize the class I get <RequestingPatient_ID xmlns="">D1234567890</RequestingPatient_ID> So each emlement contains xmlns=&qu...

VS.NET creating XSD from XML doc
Hi: I usually add the available enumerated values for an attribute into the XSD manually. Does VS.NET have the capability to fill up the enumerated values list based on the original XML document, when I right click and click on "Create Schema"? Thanks, Do ...

Open XML File Converter
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Any word on an update to this converter that resolves the &quot;There is not enough memory or disk space to convert this document&quot; bug on SL? I've seen it reported elsewhere so I'm assuming it's a known issue, but can't find any relevant info here. <br><br>Thanks. Thanks Diane, nuked all caches and removed duplicate fonts but still getting the error. ...

File Formats
Will my *.mny file from Money 2001 work with Money 2004? Provided 2001 and 2004 are from the same region. i.e. both US or both UK. -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny UK Wishes/Suggestions mnyukwsh@microsoft.com "tcolby" <tcolbymn@yahoo.com> wrote in message news:3f9101c3764f$fb2adc90$a301280a@phx.gbl... > Will my *.mny file from Money 2001 work with Money 2004? To be precise, Money 2004 will upgrade your Money 2001-compatible file to one that is Money 2004-compatible (assumin...

Pivot Table Data #2
I am using Excel 2003 and my pivot chart is working fine. I have multiple data points in the data field. My problem is I would like it to chart all of the points, not the average like I am currently using. The pivot table is setup on a line chart currently. Thank you for your help in advance, I know that there is a simple answer but I am missing it some where. Tim Hi, Pivot charts plot what Pivot tables display. So the problem is not in your chart but in the pivot table. You must set it up so it display all your point. We don't have enough info to tell you have to do it. If th...

Auto Update Pivot Table and format wrap text
I have this code in a module, so as to refresh automatically a pivot table in a sheet named "PrintSheet" it works fine in till I added some extra code to format the cell D:D. What happens is now it tries to format every sheet I open, I only what to format every time I click on the "PrintSheet" and not in all the other sheets, and also need it to format from range D6:D not the complete column. I found out that the pivot table does not keep its format properties, so when new data is added it need to be formatted everytime, that is why I like to do this auto refresh/format. ...

move a text fiele into access table field
hi everybody, i have several files in .txt, and i create a table with fichiD(autonumber) fichtext(OLE Object), i need insert all these files depending a path that user are select on dialog form and move these files into this table field. how can i make these by conding in a form? anybody help me? ...

Special cell formatting
How do you: Have a cell equal another cell and display in written numeric like a check. $5,250.30 displays Five-thousand two hundred fifty dollars and 30 cents. Thanks! "Sunshine" <Sunshine@discussions.microsoft.com> wrote in message news:DD44CFC4-A966-47E0-A477-87DCCD329A54@microsoft.com... > How do you: > Have a cell equal another cell and display in written numeric like a check. > > $5,250.30 displays Five-thousand two hundred fifty dollars and 30 > cents. > > > Thanks! "Sunshine" <Sunshine@discussions.microsoft....

Multiple Conditional Formatting rules
Can anyone tell me how I can set 20 rules for conditional formatting o one sheet? What I need to do is colour a spreadsheet with 15-20 colours accordin to a (different) spreadsheet full of numbers, so that eg 1 = red, 2 pink, 3 = blue, 4 = green..... etc... Any help would be much appreciated. Thanks -- Message posted from http://www.ExcelForum.com Hi this can only be done with VBA. Put the following code in your worksheet module Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is No...