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

Squazz · Answer 1 · 2023-08-15T10:35:48.237

125

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 deploying it to production. It is however very useful 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

edited Aug 15 '23 at 10:35

answered Apr 14 '16 at 14:08

Squazz

3,912
7
38
62

4

Ditto 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%' – Cliff Coulter Mar 10 '17 at 19:13
[Err] 42000 - [SQL Server]Conversion of one or more characters from XML to target collation impossible – digz6666 May 10 '17 at 04:32
[Err] 22018 - [SQL Server]Explicit conversion from data type xml to text is not allowed. – digz6666 May 10 '17 at 04:33
Sounds like you are doing something wrong @digz6666 – Squazz May 10 '17 at 13:02
@digz6666 have you tried nvarchar instead of just varchar? – Squazz May 11 '17 at 05:50
@digz6666 if possible, remove your downvote then? ;) – Squazz May 11 '17 at 12:30
1

@Squazz You last voted on this answer yesterday. Your vote is now locked in unless this answer is edited. :) – digz6666 May 11 '17 at 12:38

score 75 · Accepted Answer · answered Dec 02 '09 at 13:45

75

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

answered Dec 02 '09 at 13:45

marc_s

732,580
175
1,330
1,459

1

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 02 '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 02 '09 at 13:51
11

to search the whole filed: WHERE xmlField.value('.', 'varchar(max)') LIKE '%FOO%' – jhilden Apr 25 '13 at 19:03
watch out for pesky Xml namespaces if you get NULL back – Simon_Weaver Mar 15 '18 at 23:32

Carl Onager · Answer 3 · 2014-06-19T07:16:51.833

12

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%'

edited Jun 19 '14 at 07:16

answered Jun 04 '14 at 08:29

Carl Onager

4,112
2
38
66

Be advised this may bypass any selective xml indexes you may have in place and take a hit on performance. – Rudy Hinojosa Apr 10 '19 at 19:58

score 0 · Answer 4 · edited Feb 06 '19 at 09:43

0

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

edited Feb 06 '19 at 09:43

Squazz

3,912
7
38
62

answered Dec 02 '09 at 14:40

Jon

38,814
81
233
382

Do I need parameters to prevent injection somehow? – Jon Dec 02 '09 at 14:55
3

BE AWARE: those XML function ARE case-sensitive - DATA.VALUE **will not** work ! It needs be to **.value(...)** – marc_s Dec 02 '09 at 15:01

score 0 · Answer 5 · answered Aug 25 '23 at 11:45

0

The correct way to do this is to use the contains() XQuery predicate

SELECT *
FROM WebPageContent wpc
WHERE wpc.data.exist('/PageContent/Text/text()[contains(., "Test")]') = 1;

answered Aug 25 '23 at 11:45

Charlieface

52,284
6
19
43

Use a LIKE statement on SQL Server XML Datatype

5 Answers5

Linked