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.

How can I get the table schema using Entreprise Library DAAB ?

Hello people,

i'm using the DAAB (Enterprise Library) to access the data in my system. But I need to use the schema from the tables i'm reading....

when I was using the default DataAdapter, I have used the FillSchema() method, but now, when I use de DAAB to fill de data, I couldn't get the primary keys columns, unique columns neither autoincrement columns...

This is the code that i'm using to get the data with DAAB:

===========================

Database db = DatabaseFactory.CreateDatabase();

DBCommandWrapper cmd = db.GetSqlStringCommandWrapper("SELECT * FROM Customer");

ds = db.ExecuteDataSet(cmd);

===========================

But this code don't return the schema from the "Customer" table.

Do you have any tip to do it?

thanks people

Andr

Since this is not a Data Mining question, can you try posting to one of these forums?:

.NET Framework Data Access and Storage (http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=45)

SQL Server Data Access (http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=87)

Raman Iyer
SQL Server Data Mining
http://www.sqlserverdatamining.com