0

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

2

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

2

&#xD;&#xA; 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.