Use a LIKE statement 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 have a  Text  node but I need to search within that node  select   from WebPageContent where data exist   PageContent Text     1

User · Answer

Another option is to search the XML as a string by converting it to a string and then using LIKE  However as a computed column can t be part of a WHERE clause you need to wrap it in another SELECT like this   SELECT   FROM      SELECT    CONVERT varchar MAX    COLUMNA   as  XMLDataString  FROM TABLE  x WHERE  XMLDataString  like   Test

User · Answer

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   gt  0   Return a substring on the search where the search criteria exists

User · Answer

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

User · Answer

Yet another option is to cast the XML as nvarchar  and then search for the given string as if the XML vas a nvarchar field   SELECT    FROM Table WHERE CAST Column as nvarchar max   LIKE   TEST     I love this solution as it is clean  easy to remember  hard to mess up  and can be used as a part of a where clause   EDIT  As Cliff mentions it  you could use         nvarchar if there s characters that don t convert to varchar

[sql] Use a LIKE statement on SQL Server XML Datatype

Examples related to sql

Examples related to sql-server

Examples related to xml

Examples related to sql-server-2005