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