Use a LIKE statment on SQL Server XML Datatype

Question

If you have a varchar field you can easily do SELECT * FROM TABLE WHERE COLUMNA LIKE '%Test%' to see if that column contains a certain string.

How do you do that for XML Type?

I have the following which returns only rows that has a 'Text' node but I need to search within that node

select * from WebPageContent where data.exist('/PageContent/Text') = 1

Answer 1

You should be able to do this quite easily:

SELECT * 
FROM WebPageContent 
WHERE data.value('(/PageContent/Text)[1]', 'varchar(100)') LIKE 'XYZ%'

The .value method gives you the actual value, and you can define that to be returned as a VARCHAR(), which you can then check with a LIKE statement.

Mind you, this isn't going to be awfully fast. So if you have certain fields in your XML that you need to inspect a lot, you could:

• create a stored function which gets the XML and returns the value you're looking for as a VARCHAR()
• define a new computed field on your table which calls this function, and make it a PERSISTED column

With this, you'd basically "extract" a certain portion of the XML into a computed field, make it persisted, and then you can search very efficiently on it (heck: you can even INDEX that field!).

Marc

Answer 2

This is what I am going to use based on marc_s answer:

SELECT 
SUBSTRING(DATA.VALUE('(/PAGECONTENT/TEXT)[1]', 'VARCHAR(100)'),PATINDEX('%NORTH%',DATA.VALUE('(/PAGECONTENT/TEXT)[1]', 'VARCHAR(100)')) - 20,999)

FROM WEBPAGECONTENT 
WHERE COALESCE(PATINDEX('%NORTH%',DATA.VALUE('(/PAGECONTENT/TEXT)[1]', 'VARCHAR(100)')),0) > 0

Return a substring on the search where the search criteria exists