Search code examples
sqlsql-servert-sqlsqlxml

SQL : Find if XML node exists

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.

Solution

  • 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