Escape XML special characters in SQL Server Query

In this post, I'll demonstrate how to handle special characters in 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)')

Here’s an image that illustrates the output when using special characters in the XML.

SELECT s.name AS Schema_Name, o.name AS Object_Name, o.type_desc,
    (SELECT m.definition FOR XML PATH(''), type) AS 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('')) AS 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

Here’s the output when you include the 'type' keyword in the query:

Now, here’s the result without the '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;

Here’s the output of the three queries above:

For further reference, check out the following resources:

Using XML Type in SQL Queries

XML with Null Columns