7

Assuming I have a table data as below:Sample Table

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.

Gun.IO
  • 173
  • 1
  • 1
  • 9

1 Answers1

10

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
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 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` – Gun.IO Mar 13 '15 at 06:32
  • @Gun.IO You fooled me by using a XML column in the fiddle. – Mikael Eriksson Mar 13 '15 at 06:37
  • My mistake. Apologies! – Gun.IO Mar 13 '15 at 08:06
  • 1
    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'. – Ryanman Jun 12 '17 at 13:37