Use a LIKE statement on SQL Server XML Datatype
Solution 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
Solution 2
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.
This might not be the best performing solution, so think twice before deplying it to production. It is however very usefull for a quick debug session, which is where I mostly use it.
EDIT: As Cliff mentions it, you could use:
...nvarchar if there's characters that don't convert to varchar
Solution 3
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%'
Related videos on Youtube
Jon
Updated on July 16, 2021Comments
-
Jon almost 3 years
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
-
Jon over 14 yearsI'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...'
-
RickNZ over 14 yearsBeat me to it by 5 seconds. Another possibility is to consider using free text search, if your data is amenable...
-
Jon over 14 yearsDo I need parameters to prevent injection somehow?
-
marc_s over 14 yearsBE AWARE: those XML function ARE case-sensitive - DATA.VALUE will not work ! It needs be to .value(...)
-
jhilden about 11 yearsto search the whole filed: WHERE xmlField.value('.', 'varchar(max)') LIKE '%FOO%'
-
Cliff Coulter about 7 yearsDitto on that, or nvarchar if there's characters that don't convert to varchar SELECT * FROM Table WHERE CAST(Column as nvarchar(max)) LIKE '%TEST%'
-
digz6666 almost 7 years[Err] 42000 - [SQL Server]Conversion of one or more characters from XML to target collation impossible
-
digz6666 almost 7 years[Err] 22018 - [SQL Server]Explicit conversion from data type xml to text is not allowed.
-
Squazz almost 7 yearsSounds like you are doing something wrong @digz6666
-
Squazz almost 7 years@digz6666 have you tried nvarchar instead of just varchar?
-
Squazz almost 7 years@digz6666 if possible, remove your downvote then? ;)
-
digz6666 almost 7 years@Squazz You last voted on this answer yesterday. Your vote is now locked in unless this answer is edited. :)
-
aruno about 6 yearswatch out for pesky Xml namespaces if you get NULL back
-
Rudy Hinojosa about 5 yearsBe advised this may bypass any selective xml indexes you may have in place and take a hit on performance.