SQL Server - Processing a xml in sql server?(Modified)

Asked By Nagaraj muthuchamy on 22-Dec-07 04:04 AM
Hi,

I have a table which stores the xml in column named "xml_data".
The xml data has 5 levels of node.
Now, I want to extract specific Node Values from xml text.
the repeated node values should be returned as rows.
Is this possible using sql qurey?

Thank You
Nagaraj

You'll want to use the CLR for this - Robbe Morris replied to Nagaraj muthuchamy on 22-Dec-07 03:02 PM

Standard SQL syntax will not parse the xml for you.  Here is a sample of how to incorporate the CLR in sql server.

http://www.eggheadcafe.com/articles/sql_server_2005_clr_regex.asp

Storing blocks of xml is fine if you just want to retrieve it.  Otherwise, it is very

poor design if you need to parse that xml as part of a WHERE clause in a query.