How to remove special characters in the xml?

SQL Server Administration
1

I extracting schema for procedure and functions using below query as xml

SELECT      SPECIFIC_SCHEMA     AS '@ObjectSchema'      ,ROUTINE_NAME       AS '@ObjectName'      ,ROUTINE_TYPE       AS '@ObjectType'      ,ROUTINE_DEFINITION AS '@TEXT'FROM  INFORMATION_SCHEMA.ROUTINES WHERE   (ROUTINE_TYPE = 'function' OR ROUTINE_TYPE = 'procedure')For XML PATH ('Object') , TYPE, ROOT('FuncProc')

I getting the xml with special characters like 


<FuncProc> <Object ObjectSchema="dbo" ObjectName="greetings" ObjectType="PROCEDURE" TEXT="CREATE PROCEDURE greetings &#xD;&#xA;AS &#xD;&#xA;BEGIN &#xD;&#xA; dbms_output.put_line('Hello World!'); &#xD;&#xA;END; &#xD;&#xA;/&#xD;&#xA;" /></FuncProc>

How to avoid the special characters?

Thanks in advance

3

You can always use replace if those are the 2 values

declare @x xml = '<FuncProc> <Object ObjectSchema="dbo" ObjectName="greetings" ObjectType="PROCEDURE" TEXT="CREATE PROCEDURE greetings &#xD;&#xA;AS &#xD;&#xA;BEGIN &#xD;&#xA; dbms_output.put_line(''Hello World!''); &#xD;&#xA;END; &#xD;&#xA;/&#xD;&#xA;" /></FuncProc>'

select cast(Replace(cast(@x as varchar(max)),'&#x0D;&#x0A;','') as xml)