Select specific rows with SQL Server XML column type

Question

I'm trying to select data from a table defined similar to the following :

Column     |    Data Type
-------------------------
Id         |    Int
DataType   |    Int
LoggedData |    XML

but I only want to select those rows with a specific DataType value, and that contain a string (or evaluates a piece of XPath) in the LoggedData column.

A quick Google search turned up nothing useful, and I am in a bit of a rush to get an answer... I'll carry on searching, but if anyone can help me out on this in the mean time, I'd really appreciate it.

EDIT _ Clarification

So, what I'm after is something like this, but in the correct format...

select Id, LoggedData from myTable where DataType = 29 and LoggedData.query('RootNode/ns1:ChildNode[@value="searchterm"]');

Still might not be clear...

Answer 1

Is this not doing the trick for you?

SELECT
  Id, 
  LoggedData 
FROM
  myTable 
WHERE
  DataType = 29 
  AND LoggedData.exist('RootNode/ns1:ChildNode[@value="searchterm"]') = 1

Answer 2

If you want to filter by a searchterm (like a SQL variable) you will probably need to use something like this:

Select Id, LoggedData From myTable Where DataType = 29 And 
LoggedData.exist('RootNode/ns1:ChildNode[@value=sql:variable("@searchterm")]')=1

where @searchterm is your SQL variable.