Use a LIKE statment on SQL Server XML Datatype?

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.

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.

I'm basically implemeting a search feature so I want to search the XML column only on the 'Text' nodes and then return a substring to indicate that the search has found a match. For example search on 'hi there' instead of returning the whole xml column I'd just return a substring such as 'the chap said hi there and carried...' – Jon Dec 2 '09 at 13:51 1 Beat me to it by 5 seconds. Another possibility is to consider using free text search, if your data is amenable... – RickNZ Dec 2 '09 at 13:51.

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.

– Jon Dec 2 '09 at 14:55 BE AWARE: those XML function ARE case-sensitive - DATA. VALUE will not work! It needs be to .

Value(...) – marc_s Dec 2 '09 at 15:01 1 Just spotted that – Jon Dec 2 '09 at 15:12 OK, just to make sure you know why it might not work :-) – marc_s Dec 2 '09 at 15:49.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.