By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,354 Members | 907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,354 IT Pros & Developers. It's quick & easy.

Searching through DB Table containing Records in XML format

P: n/a
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
Feb 10 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Feb 10 '06 #2

P: n/a
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


Feb 10 '06 #3

P: n/a
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."

Feb 13 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.