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

Similar Articles

[PageSpeed] 3

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:

Paragraph formating jumps to defaults.
Hi, We are using Office/Outlook 2007 We have an email signature script that is run when users login, it´s working ok except for two things; If the default paragraph settings in Word 2007 for a user matches those in the signature script, the signature will use the "Base defaults" of Word 2007, that is SpaceAfter = 10 and LineSpacing = "Multiple" with a value of 1,15. So if I change the script to use SpaceAfter=0 and the user has set his/her Word 2007 to use SpaceAfter=0, the result will be SpaceAfter=10. If the script is set to SpaceAfter=0 and the us...

How to link with an Access DB
Hi, I have an Acces DB with many tables. I need to choose the name of a customer in a cell of Excel. For example, in acces I have this tables: Table1 Id Name Last Name City I need to choose the last name from a drop down menu in a spreadsheet and then in other cell I need to put all the data regarding the last name that I choose. I hope to be exaustive, and sorry for my english. :-) Many Thanks Stefano ...

Tracking shared records
I need to create a report that shows which records are shared and with whom. Can someone guide me in the right direction? I can't seem to find it anywhere online. thank you Sharing is recorded in the PrincipalObjectAccess table in the SQL database. Advanced find does not allow you to access this so you will have to create a report on the directly on the database. -- Patrick Verbeeten (MCPD) Lead Developer Aviva IT Extended Entity and Plug-in browser: http://www.patrickverbeeten.com/maps/CrmTool.aspx "Bernardina" wrote: > I need to create a report that shows whi...

Proper Table(s) Layout
I have created a couple of Dbs which the table structure seemed to simply fall into place, it was just logical in my head. That said, I am working on a new db and for some reason I am doubting myself and wanted a second opinion. The db is basically a contract db to input all the info, and there is a lot of info, for each contract. Where I am 'lost' is the fact that the contracts are broken into categories: clients, components,engineering, warantee... For all of the components (with the exception of clients) there are a number of fields but only 1 entry per contract....

Where is the lasso feature in 2008? (was in formatting palette in 2004)
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, <br><br>In Excel 2004 in the formatting palette in image, there are different selection marquees and lasso's. Where are these features in 2008 Excel? I can't find them anywhere. <br><br>thnx I just found my answer, the &quot;genius's&quot; at Microsoft / MAC Office decided to kill off all these editing features... <br><br>Over $200 to upgrade to a product with LESS features... I don't think so... unbelievable, I will continue to use 2004 because 2008...

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

formatting auto replies
Does anyone know of a method that allows outlook 2k2 to format automatic replies regardless of the format of the original message? I setup this automatic response rule that replies with a template, but problem is, when replying to http clients (hotmail or yahoo), the message at the receiver's end has words totally misplaced, all over the page. Any suggestions would be appreciated. ...

Table link documentation
I am having trouble trying to locate A/P check data that has project related costs. I found the check data but it is does not indicate the projects, I found the project data but can not determine thye logical link between the two tables, I may be using the wrong tables the tables I am using are PM80500 and PA31102. Is there any documentation of how all the tables in the system are logically link. I am trying to write reports in MS Access, but there are 1500+ tables in GP (version 10) -- Dave F In an effort to find the correct table you can do a number of things (believe me I do)....

Duplicate record in RM tables
We experienced an issue in Apply Sales Document that may have caused a duplicate record somewhere. We found this when running Paid Sales Transaction Removal and received this message: Violation of PRIMARY KEY contraint PKRM3101. Cannot insert duplicate key in object RM30101. I ran the RM duplicate tool found in the automated help area of this website and found the following: --- Begin copy here ---- Duplicates between RM Open and RM History Document #: 07-003021-17 Customer #: 079100 RMDTYPAL #: 7 --- End copy --- It looks like the duplicte tool also logs the qu...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

XML selectsinglenode
Hi I have an XML file i need to load and read the contents. Here is the top part of the xml file. <Research xsi:schemaLocation="http://www.rixml.org/2002/6/RIXML http://GreenJAR/DAVCatalog/Dashboards/GreenJAR/Documents/Schemas/RiXML2.xsd" researchID="" createDateTime="2003-07-10T00:00:00" language="eng" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.rixml.org/2002/6/RIXML"> <Product productID="BJZABULLETIN_0711" eventIndicator="No" sequence="0"> <StatusInfo sta...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

Format for credit card numbers
I've tried a custom format for entering credit card numbers (four groups of four digits, separated by dashes: xxxx-xxxx-xxxx-xxxx. I have tried to use ####-####-####-#### and 0000-0000-0000-0000 but each of these causes the last digit to change to zero. So if I enter 5415779800902512 I get 5415-7798-0090-2510. Anybody already solved this problem? -- Schmacker ------------------------------------------------------------------------ Schmacker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28041 View this thread: http://www.excelforum.com/showthread.php?th...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

Error loading XML into DataSet
Hello, When I try to load this xml file (see below) into an asp.net dataset using the following code: ds.ReadXml("xmlfilepath\xmlfile.xml", XmlReadMode.Auto) I get the following error: "The same table (description) cannot be the child table in two nested relations. " If I take out the anchor tag in the description it works fine, however, I need the anchor tag in there. Does anyone have any suggestions? <?xml version="1.0" ?> <rss version="2.0"> <channel> <title>Liftoff News</title> <link>http://liftoff.msf...

Varying format
Is there any way to format a cell differently depending on the number of decimal places typed in? What I need is a minimum of 2dp but 3 if the typed entry matches. eg 1.2 appears as 1.20 and 1.234 appears as typed. TIA Ian Hi! Try formatting the cell with a custom number format such as 0.00## Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478 View this thread: http://www.excelforum.com/showthread.php?threadid=27494 "AlfD" <AlfD.1f5q6o@excelforum-nospam.c...

Pivot Table Defaults
In the pivot table field list, whenever I create a new pivot table and I am inserting fields into the value area, I generally get as default field setting the 'Count' value. Is there a way to format the spreadsheet to make Excel recognize the data as all numbers so it defaults to the "Sum" function as opposed to "text"? Hi The rule that the PT Wizard adopts is, If all the values in the field being added to the data area are Numeric, then it uses Sum. If any of the values are Text or BLANK, then it uses Count. It sounds as though you have defin...

Like a pivot table
Hello every body I'm first time requesting in this group, so I opologize in advance for any mistakes or something annoying I repeat what I have sent before 10 min because I see it unclear when it goes to news group If any one can help me I'm working with data which most of it comes like a table with feilds as columns and records as rows. I want it to be as many rows with each feild an example what is exist name age Joining Date Tele John 20 Jun-90 4321251 Iqbal 30 Jul-95 6583752 George 40 Sep-85 7843125 What I wa...

Setting appointment start & end time increments format
In Outlook 2002, I can easily set the Day View to display the time increments in 15 minute blocks, but on the Appointments form, the Start and End Times available on the drop-down menus, respectively, are in 30 minute increment blocks only. How can I set the format for the appointment page to have the Start and End Times for appointments have increment time blocks of 15 minutes? Thanks! My direct email address is: solutioncounselor@comcast.net . If you set your appointments in 15 minute blocks Outlook will start to offer that. It takes a few uses to work. -- Nikki Peterson [MVP ...

searching a cell for a contained text word
Is it possible to search a cell for a key word or words contained in text made of multiple words enabling the user to than create a pivot table using the collected key word or words as data? Doug K ...

Time Format Question
Hello, I currently have time entered like this: 173517 90207 I need to look like this: 5:35 p.m. 9:02 a.m. I've done a ton of research but nothing has worked so far. Thank you. With your values in column AA, try: =TIME(LEFT(A1,LEN(A1)-4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2)) -- Gary''s Student - gsnu201003 "Toria" wrote: > Hello, > I currently have time entered like this: > 173517 > 90207 > > I need to look like this: > 5:35 p.m. > 9:02 a.m. > > I've done a ton of research but nothing has worked so fa...

difficult format
I want to format a code like this: ##.0001.## so then i push it down and gives me 0002 and so on, the other ## r numbers. The problem is that when i do that, it sums in the last two numbers instead of what i want, so i ask if it is possible to format like this: (text)##.(number)0001.(text)## so it guives me the sum of number then do what i want. if it is possible how ca i do that? Thanks You can try this, starting in row 1: ="##."&TEXT(ROW(),"0000")&".##" You'll have to adjust the "Row()" number when starting in any other row. For example,...

how do I change date format in the header in Excel XP ?
I need to chage my date format, in the header to Day; Month DD, YYYY ie. Saturday; May 23, 2005. Woudl you please help me out ? Thank you. Regards, Hesam Shakourian Check this out. http://support.microsoft.com/?kbid=213742 but change the format to "dddd, MMMM dd, yyyy" "Hesam" <Hesam@discussions.microsoft.com> wrote in message news:93495F75-4196-4208-9C0D-E800BCAE3A89@microsoft.com... > I need to chage my date format, in the header to Day; Month DD, YYYY > ie. Saturday; May 23, 2005. > > Woudl you please help me out ? > > Thank you. > &g...

Formula to display nearest following Thursday in mm/dd/yyyy format
Hello, I have been reading and trying different suggestions here to no avail. What I need is a formula to calculate the nearest following thursday, and display it in mm/dd/yyyy format. To be clear, I have a column of varying dates. I need a formula to return the next thursday for each of those dates. To illustrate, say I have 05/22/2010, 05/23/201, 05/24/2010, & 05/26/2010 in cells A1 through A4. In cells B1 through B4, I would like to see 05/27/2010, 05/27/2010, 05/27/2010, & 05/27/2010 representing the following thursday. Thank you for your help! BW T...

Duplicating one Field from One table to Another
Hi - I have two tables - one position, one personnel - which has a 1-to-many relationship (1 position record to many personnel records). The department had a new requirement which made it necessary to change some coding (I inherited this). I'm using tab forms so that when a position is pulled up, you can click on the tab that has the personnel information (if there is any). There is a button on the Personnel form that allows the user to add a new Personnel record. Since I am using an Auto-number field in the Position table (which doubles as the PK) the functionality is fine. Wh...