Execution Plan StatementText contains incorrect syntax

Question

I have run an execution plan with management studio 2014 on mssql 2008 R2. To be able to better read the Selectstatement i right-clicked the plan to view it in xml.

I noticed that some statements seem to contain invalid charachters SELECT @IsHeadVersion = IsHead
FROM myTable. At least if i copy them into a new query window i get an invalid syntax error

 <StmtSimple StatementCompId="10" StatementEstRows="1" 
 StatementId="2" StatementOptmLevel="TRIVIAL" 
 StatementSubTreeCost="0.0032831" 
 StatementText="SELECT @IsHeadVersion = IsHead&#xD;&#xA;FROM myTable
 WHERE Id = @VersionID&#xD;&#xA;&#xD;&#xA;
 -- if head and no delta, select platformIDs and vehicleIDs in this version&#xD;" 
 StatementType="SELECT" QueryHash="0x29CDF8384A77017F" QueryPlanHash="0xF39275675F085D1B">
 <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" 
 ANSI_WARNINGS="true" ARITHABORT="true" 
 CONCAT_NULL_YIELDS_NULL="true" 
 NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

The Statment is dynamically generated inside a stored procedure.

My qustion is could the invalid characters be added by management studio in the xml view?

Or do i have incorrect escape sequences inside the generated sql

Answer 1

_{Community Wiki answer generated from question comments}

These are not "invalid characters". This is a side-effect of formatting the plan as XML; some characters have special meaning in XML and must be encoded.

To read the query text:

1. Save the XML plan as a *.sqlplan file
2. Reopen it in SQL Server Management Studio

3. Right click in the plan window and select "Edit Query Text...":

   Screenshot

• Rushikesh: Please note "Edit Query Text" has limit of 4000 chars and it will display 1st 4000 char only. This sqlserverfast.com/blog/hugo/2018/03/… might help with more details.

Answer 2


 are carriage return/line feed. You could use SSMS find/replace to either remove them, or set them to start new lines. Just use the regular expression option. Replacing data using regular expressions