parsing XML string in a text field

I am trying to build a query on a SQL2000 text field which stores XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4</
usedby></history>
<androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegr oups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4
</usedby></history>
>

parsing XML string in a text field

I am trying to build a query on a SQL2000 text field which stores XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>nul
l</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</
threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user4</
usedby></history><androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegroups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>
use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>use
r2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er4
</usedby></history>
>

Parellism not working

We have migrated databases from SQL7 to SQL2000 Standard Edition. Now,
when we run queries against the newer hardware running SQL2000 we
notice that the execution plan shows no parallelism taking place. The
SQL2000 config. has 2 processors and both procs are enabled in the
Processor tab.(use all available processors is selected). I've
upgraded the install to SQL2000 SP3a but there is no change. Does
anyone know what may be going on here?

Thanks,
GCMost queries will not benefit from parallelism. The optimizer will
choose a parallel plan only if it makes sense for a particular query and
the server is not overtaxed.

In many cases, a parallel plan is an indication that you need to do some
index tuning.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Garry Clarke" <gclarke@.euro.banta.com> wrote in message
news:fed38413.0311180934.6f7b8540@.posting.google.c om...
> We have migrated databases from SQL7 to SQL2000 Standard Edition. Now,
> when we run queries against the newer hardware running SQL2000 we
> notice that the execution plan shows no parallelism taking place. The
> SQL2000 config. has 2 processors and both procs are enabled in the
> Processor tab.(use all available processors is selected). I've
> upgraded the install to SQL2000 SP3a but there is no change. Does
> anyone know what may be going on here?
> Thanks,
> GC

Parameters that depend on each other

I'm developing a report using SQL2000 which has Agent and SubAgent
parameters. The client wants the Agent parameter selection to filter the
SubAgent, which is easy using cascading parameters, but they also want the
SubAgent selection to filter the Agent list to show those Agent's the
selected SubAgent has worked with. Is there any way of referencing parameters
which are higher up the parameter list without getting a "Forward
dependencies are not valid." error?That sounds like a circular reference which would not work, of course...
you might add a third parameter, to list the Agents the subagent has worked
with... continuing down with the cascading parameter...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Swamp Dog" wrote:
> I'm developing a report using SQL2000 which has Agent and SubAgent
> parameters. The client wants the Agent parameter selection to filter the
> SubAgent, which is easy using cascading parameters, but they also want the
> SubAgent selection to filter the Agent list to show those Agent's the
> selected SubAgent has worked with. Is there any way of referencing parameters
> which are higher up the parameter list without getting a "Forward
> dependencies are not valid." error?
>|||Thanks for the response, Wayne.
That sounds like the only way to do it. I'll check with the client to see if
they are happy with having two different Agent parameters, or if they'd
prefer a more usual Agent-SubAgent relationship.
Regards,
Simon.
"Wayne Snyder" wrote:
> That sounds like a circular reference which would not work, of course...
> you might add a third parameter, to list the Agents the subagent has worked
> with... continuing down with the cascading parameter...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Swamp Dog" wrote:
> > I'm developing a report using SQL2000 which has Agent and SubAgent
> > parameters. The client wants the Agent parameter selection to filter the
> > SubAgent, which is easy using cascading parameters, but they also want the
> > SubAgent selection to filter the Agent list to show those Agent's the
> > selected SubAgent has worked with. Is there any way of referencing parameters
> > which are higher up the parameter list without getting a "Forward
> > dependencies are not valid." error?
> >