Select XML values when attribute involved

Question

I have a table that stores XML values in field: OtherData.

An XML value from one row:

< file path="C:\WINDOWS\system32\svchost.exe" />

I would like to select all rows that have an attribute path like '%svchost%'

What is the right SQL query to do this?

Answer 1

Use the XQuery CONTAINS method, like this:

SELECT CASE @x.exist
(
N'/bikes/Product[1]/Specifications[contains(., "Novice")]'
)
WHEN 1 THEN N'The bike is good for novices'
WHEN 0 THEN N'The bike is for more advanced riders'
END;

Good luck.

• gbn: Can you add a reference to where you copy/pasted that please? Not least for more information

Answer 2

I used an XQUERY approach but different from yours (@Allen White):

Select * From Table where OtherData.value('(/file/@path)[1]', 'varchar (1000)') LIKE '%svchost.exe%'