选择具有SQL Server XML列类型的特定行 [英] Select specific rows with SQL Server XML column type

问题描述

我正在尝试从定义类似于以下内容的表中选择数据:

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

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

但是我只想选择具有特定DataType值的行，并且这些行在LoggedData列中包含一个字符串(或评估一个XPath).

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.

快速的Google搜索没有发现任何有用的信息，我急于得到答案...我会继续搜索，但是如果在此期间有人可以帮助我，我会真的很感激.

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.

编辑 _澄清

所以，我追求的是这样的东西，但是格式正确...

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"]');

仍然不清楚...

推荐答案

如果要按搜索项(如SQL变量)进行过滤，则可能需要使用以下内容:

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

其中@searchterm是您的SQL变量.

where @searchterm is your SQL variable.

这篇关于选择具有SQL Server XML列类型的特定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！