anthony.green (11/5/2012)
The code is sound when looked at in the editior but due to it being a XML character it strips it out in the main view.If you remove the spaces in the ' & # x 0 D ; ' part of the script it will work. The script has been amended to ensure it shows that part if it now.
There's an even easier approach. Just leave CHAR(13) out of your SQL scripts that are constructed using FOR XML. Both CHAR(13) and CHAR(10) are treated as whitespace and whitespace is all equivalent in SQL scripts. FOR XML entitizes CHAR(13), but does not entitize CHAR(10) and the XML editor in SSMS will treat CHAR(10) the same as CHAR(13) + CHAR(10). You gain nothing by including CHAR(13) and create problems by doing so. Just leave it out.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA