选择具有SQL Server XML列类型的特定行

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.

但我只想选择那些具有特定DataType值的行,并且在LoggedData列中包含一个字符串(或计算一段XPath)。

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...

仍然可能不清楚......

2 个解决方案

#1

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

如果你想通过searchterm(比如SQL变量)进行过滤,你可能需要使用这样的东西:

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.

其中@searchterm是你的SQL变量。

#2

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

#1