Rexexp search

Select * from table where field_name REGEXP 'role>\n.*status>TERMED<'

I'm trying to parse xml that I've stored in a field using regexp. role> should be followed by a newline and then <status>TERMED< would be on the very next line. The query I have listed above doesn't work. Any suggestions?

 

SELECT * FROM table WHERE field_name REGEXP 'role>\r\n[\t*| *]<ns:status>TERMED<' turned out to be the answer. Didn't realize the \r's were there (even though I should have).

 

Hi, if you use MySQL 5.1.5 or up you can try xml functions

 

I tried xml functions but couldn't get it to work. The regexp works, but it would be nice to get xml parsing to work, too. SELECT * FROM table where EXTRACTVALUE(id_xml, '/ns:customer/ns:status') = 'TERMED'";
????