Search code examples
sqlsql-serverxmlsql-server-2005

Use a LIKE statement on SQL Server XML Datatype

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 have a 'Text' node but I need to search within that node

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

  • 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