Argument data type xml is invalid for argument 1 of like function

Ekrem Önsoy December 16, 2018 MSSQL

“Argument data type xml is invalid for argument 1 of like function”

You may encounter an error when you query an XML field using the LIKE operator.

For example, suppose you have a table named “Books” with fields such as “CategoryID INT”, “PublishingHouse NVARCHAR (70)”, “Book XML”.

You will encounter this error if you run a query in this table as follows:

SELECT CategoryID,PublishingHouse FROM Books WHERE Book LIKE '%dbtut%'

1	SELECT CategoryID,PublishingHouse FROM Books WHERE Book LIKE '%dbtut%'

Because you cannot directly query the XML column with the LIKE operator.

To run the query without error, you can convert the XML column to NVARCHAR:

SELECT CategoryID, PublishingHouse FROM Books WHERE (CAST(Book AS NVARCHAR(MAX)) LIKE '%dbtut%'

1	SELECT CategoryID, PublishingHouse FROM Books WHERE (CAST(Book AS NVARCHAR(MAX)) LIKE '%dbtut%'

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux