how to escape the character '&'

Question

We are getting the following error when the escape character '&' used in the XQuery.

Query: SELECT t.RECID FROM FBNK_CAMB044 t WHERE XMLRECORD.exist(N'/row[some $t in c4/text() satisfies contains($t, "H&H")]') = 1

Description: XQuery [FBNK_CAMB044.XMLRECORD.exist()]: The character '"' may not be part of an entity reference
SQLErrorInfo: 42000
Source: Microsoft SQL Server Native Client 11.0
File: dbProcess.cpp, Line: 3688

Please let me know how to escape the character '&'.

Answer 1

Hi @Parvathy Priya A ,

Welcome to Microsoft Q&A!

Please have a try with below and check whether any of them is helpful:

1. Replacing H&H with <![CDATA[H&H]]>.
2. Replacing H&H with H\&H.
3. Using & instead of just &.

Please refer to below forum for more details.
How to escape ampersand in MS SQL

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Parvathy Priya A ,

Please try the following solution.

SQL

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, XMLRECORD XML);  
INSERT INTO @tbl (XMLRECORD) VALUES  
(N'<row>  
	<c4>sun</c4>  
	<c4>H&amp;H</c4>  
	<c4>moon</c4>  
</row>'),  
(N'<row>  
	<c4>forest</c4>  
	<c4>Tom &amp; Jerry</c4>  
	<c4>tree</c4>  
</row>');  
  
-- Method #1  
SELECT *   
FROM @tbl  
WHERE XMLRECORD.exist('/row[some $t in c4/text() satisfies contains($t, "H&amp;H")]') = 1;  
  
-- Method #2  
SELECT *   
FROM @tbl  
WHERE XMLRECORD.exist('/row[c4/text() = "H&amp;H"]') = 1;