How can I parse text held in MS SQL 2005 text field

Hi,
I been reading various web pages trying to figure out how I can extract some simple information from the XML below, but at present I cannot understand it.

I have a MS SQL 2005 database with which contains a field of type text (external database so field type cannot be changed to XML)
The text field in the database is similar to the one below but I have simplified it by remove many of the unneeded tags in the <before> and <after> blocks. I also reformatted it to show the structure (original had no spaces or returns)

For each text field in the SQL table contain the XML I need to know the OldVal and the NewVal.

<ProductMergeAudit>
<before>
<table name="table1" description="Test Desc">
<product id="OldVal">
</table>
</before>
<after>
<table name="table1" description="Test Desc">
<product id="NewVal">
</table>
</after>
</ProductMergeAudit>

Cast your TEXT to an XML datatype field and use:

SET

@.XML=CONVERT(XML,'<ProductMergeAudit>
<before>
<table name="table1" description="Test Desc">
<product id="OldVal"/>
</table>
</before>
<after>
<table name="table1" description="Test Desc">
<product id="NewVal"/>
</table>
</after>
</ProductMergeAudit>')
SELECT @.XML.value('(/ProductMergeAudit/before/table/product/@.id)[1]','varchar(50)'), @.XML.value('(/ProductMergeAudit/after/table/product/@.id)[1]','varchar(50)')

gives

OldVal NewVal

N.B. The line<product id="NewVal"/> had to have a / added at the end to make it valid XML.

|||

Thanks,
That work when selecting the value from a single row into a XML variable.