SQL : Find if XML node exists

Question

Assuming I have a table data as below:

I want to Select all Value(XML Data) which contains the node Name="Hello World". How can I achieve it?

SQL Fiddle

set @f = @XML.exist('/ArrayOfFilterColumn/SelectColumn[(@Name) eq "Hello World"]');
select @f;

I am not sure how could I add it in my where condition, so I have put it in a fiddle.

Reportage

Answer 1

Skip the use of an XML variable and put the exist in the where clause when you query the table.

select F.Value
from XML_FILES as F
where F.Value.exist('/ArrayOfArrayOfSelectColumn/SelectColumn[@Name eq "Hello World"]') = 1

Your column is apparently text so you need to change that because text is deprecated and has been for quite some time.

ntext, text, and image (Transact-SQL)

ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

In your case you should of course change to XML instead.

Until you fix that you can cast to XML in your query.

select F.Value
from XML_FILES as F
where cast(F.Value as xml).exist('/ArrayOfArrayOfSelectColumn/SelectColumn[@Name eq "Hello World"]') = 1

Lac

My column Value is of type text. So it is returning me an error as Cannot call methods on text. My mistake as in my fiddle I have mentioned it as type XML – Reportage

@Reportage You fooled me by using a XML column in the fiddle. – Lac

My mistake. Apologies! – Reportage

For Posterity - Mikael's answer is correct. However I did want to mention that you shouldn't get confused by the '.Value' part of the answer, as the XML function .value is NOT what's being called here. Instead, the name of the column where we're checking for existence of a node is called 'Value'. – Participation