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 & (which is escaped in XML). When I extract that element to VARCHAR I receive & 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&para2=dasdasdfdas&param3. It is a part of query string. I converted it to XML and send it as a part of XML structure:

  aaa
  param1=xyz&para2=dasdasdfdas&param3

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 (&-s are not escaped - &).

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

CAST( ... AS XML);

Because &-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.

StackExchange Database Administrators Q#143132, answer score: 13