Escape XML special characters in SQL Server Query

Here I am trying to how to work with special character in a SQL Server.

DECLARE @xml XML = '<zzz xmlns="http://himanshupatel.in"><aaa>aaa</aaa> <bbb>param1=xyz&amp;para2=dasdasdfdas&amp;param3</bbb></zzz>'

SELECT @xml [before], DATALENGTH(@xml) dl

;WITH XMLNAMESPACES ( DEFAULT 'http://himanshupatel.in' )
SELECT @xml.value('(zzz/bbb/text())[1]', 'VARCHAR(100)')

SELECT s.name AS Schema_Name     ,  o.name AS Object_Name,  o.type_desc
    ,  (SELECT m.definition FOR XML PATH(''), type)  Object_Definition
FROM sys.sql_modules m
INNER JOIN sys.objects o     ON m.object_id = o.object_id
INNER JOIN sys.schemas s     ON o.schema_id = s.schema_id


SELECT s.name AS Schema_Name     ,  o.name AS Object_Name,  o.type_desc
    ,  (SELECT m.definition FOR XML PATH('') )  Object_Definition
FROM sys.sql_modules m
INNER JOIN sys.objects o     ON m.object_id = o.object_id
INNER JOIN sys.schemas s     ON o.schema_id = s.schema_id

Result of query (include type keyword)

Result of query (without type keyword)

 select (select ', <' + name + '>' from sys.databases where database_id > 4 order by name for xml path(''), type )

 select (select ', <' + name + '>' from sys.databases where database_id > 4 order by name for xml path(''))

 select  stuff( (select ', <' + name + '>' from sys.databases where database_id > 4 order by name for xml path(''), type ).value('(./text())[1]','varchar(max)') , 1, 2, '') as namelist;
 

Result of above 3 query

Other Refrence

How to work with xml type using Query

Xml with null Column