How to escape XML characters in TSQL before converting to XML? I have XML structure which is input param for my stored procedure. It contains element with &amp; (which is escaped in XML). When I extract that element to VARCHAR I receive &amp; which is not valid XML character. I need to escape it, before converting to XML again. How to do that without REPLACE?

I have the following text: param1=xyz&amp;para2=dasdasdfdas&amp;param3. It is a part of query string. I converted it to XML and send it as a part of XML structure:

&lt;zzz xmlns="http://example.com"&gt;
  &lt;aaa&gt;aaa&lt;/aaa&gt;
  &lt;bbb&gt;param1=xyz&amp;amp;para2=dasdasdfdas&amp;amp;param3&lt;/bbb&gt;
&lt;/zzz&gt;


Inside the stored procedure I need to extract it. I do that with:

ISNULL(NULLIF(LTRIM(RTRIM(@XMLInput.value('declare default element namespace "http://example.com"; (zzz/bbb)[1]', 'NVARCHAR(250)'))), ''), '');


I after that the value contains normal text (&amp;-s are not escaped - &amp;).

After some processing I need to put that string inside other XML. I make:

CAST( ... AS XML);

Because &amp;-s are not escaped I got error.
Use for xml path to create XML instead of casting.

select @YourVariable for xml path(''), type


Empty string in the path expression and the absence of an alias on the returned column will give you your string back as xml.