| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="INSERT #T 
 (Fruit)
SELECT 
 N.n.value('local-name(.)', 'nvarchar(10)') 
FROM @xml.nodes('/Fruits/*') AS N (n)
ORDER BY
 ROW_NUMBER() OVER (ORDER BY N.n)" StatementId="1" StatementCompId="4" StatementType="INSERT" RetrievedFromCache="true" StatementSubTreeCost="67.0162" StatementEstRows="66" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x5E9CDF5376C29D70" QueryPlanHash="0x035BAAB2A408EC52" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="48" CompileTime="0" CompileCPU="0" CompileMemory="712"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(64),[Expr1012],0)"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1906056"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Table Insert" LogicalOp="Insert" EstimateRows="66" EstimateIO="0.01" EstimateCPU="6.6e-05" AvgRowSize="9" EstimatedTotalSubtreeCost="67.0162" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[tempdb]" Schema="[dbo]" Table="[#T]" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[#T].[Fruit] = RaiseIfNullInsert([Expr1015]),[#T].[ID] = [Expr1016]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="[#T]" Column="Fruit"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullInsert"><ScalarOperator><Identifier><ColumnReference Column="Expr1015"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Table="[#T]" Column="ID"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1016"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="66" EstimateIO="0" EstimateCPU="6.6e-06" AvgRowSize="25" EstimatedTotalSubtreeCost="67.0061" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1015"></ColumnReference><ColumnReference Column="Expr1016"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar ComputeSequence="1"><DefinedValues><DefinedValue><ColumnReference Column="Expr1016"></ColumnReference><ScalarOperator ScalarString="getidentity((-1187042016),(2),N'#T')"><Intrinsic FunctionName="getidentity"><ScalarOperator><Const ConstValue="(-1187042016)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="N'#T'"></Const></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="66" EstimateIO="0" EstimateCPU="6.6e-06" AvgRowSize="29" EstimatedTotalSubtreeCost="67.0061" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1015"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1015"></ColumnReference><ScalarOperator ScalarString="[Expr1014]"><Identifier><ColumnReference Column="Expr1014"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="66" EstimateIO="0" EstimateCPU="0.00027588" AvgRowSize="29" EstimatedTotalSubtreeCost="67.0061" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1014"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></OuterReferences><RelOp NodeId="4" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="66" EstimateIO="0" EstimateCPU="5.28e-06" AvgRowSize="469" EstimatedTotalSubtreeCost="1.00406" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="66" EstimateIO="0" EstimateCPU="1.32e-06" AvgRowSize="469" EstimatedTotalSubtreeCost="1.00406" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference><ColumnReference Column="Segment1019"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy></GroupBy><SegmentColumn><ColumnReference Column="Segment1019"></ColumnReference></SegmentColumn><RelOp NodeId="6" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="66" EstimateIO="0" EstimateCPU="5.6e-05" AvgRowSize="461" EstimatedTotalSubtreeCost="1.00406" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="1"><RelOp NodeId="7" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="200" EstimateIO="0" EstimateCPU="1.004" AvgRowSize="461" EstimatedTotalSubtreeCost="1.004" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></OutputList><MemoryFractions Input="0" Output="0"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Table="[XML Reader with XPath filter]"></Object><ParameterList><ScalarOperator ScalarString="[@xml]"><Identifier><ColumnReference Column="@xml"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"></Const></ScalarOperator><ScalarOperator ScalarString="NULL"><Const ConstValue="NULL"></Const></ScalarOperator><ScalarOperator ScalarString="NULL"><Const ConstValue="NULL"></Const></ScalarOperator></ParameterList></TableValuedFunction></RelOp><Predicate><ScalarOperator ScalarString="[@xml] IS NOT NULL"><Identifier><ColumnReference Column="ConstExpr1017"><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Column="@xml"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Predicate></Filter></RelOp></Segment></RelOp></SequenceProject></RelOp><RelOp NodeId="8" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.1e-06" AvgRowSize="21" EstimatedTotalSubtreeCost="66.0017" Parallel="0" EstimateRebinds="65" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1014"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1014"></ColumnReference><ScalarOperator ScalarString="MIN(CASE WHEN [@xml] IS NULL THEN NULL ELSE CASE WHEN datalength(CONVERT_IMPLICIT(sql_variant,CONVERT_IMPLICIT(nvarchar(64),CONVERT_IMPLICIT(nvarchar(max),isnull(CONVERT_IMPLICIT(nvarchar(4000),XML Reader.[tagname],0),N''),0),0),0))>=(128) THEN CONVERT_IMPLICIT(nvarchar(10),CASE WHEN datalength(CONVERT_IMPLICIT(nvarchar(max),isnull(CONVERT_IMPLICIT(nvarchar(4000),XML Reader.[tagname],0),N''),0))<(128) THEN NULL ELSE CONVERT_IMPLICIT(nvarchar(max),isnull(CONVERT_IMPLICIT(nvarchar(4000),XML Reader.[tagname],0),N''),0) END,0) ELSE CONVERT_IMPLICIT(nvarchar(10),CONVERT_IMPLICIT(sql_variant,CONVERT_IMPLICIT(nvarchar(64),CONVERT_IMPLICIT(nvarchar(max),isnull(CONVERT_IMPLICIT(nvarchar(4000),XML Reader.[tagname],0),N''),0),0),0),0) END END)"><Aggregate Distinct="0" AggType="MIN"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Column="@xml"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="GE"><ScalarOperator><Intrinsic FunctionName="datalength"><ScalarOperator><Convert DataType="sql_variant" Style="0" Implicit="1"><ScalarOperator><Convert DataType="nvarchar" Length="128" Style="0" Implicit="1"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Intrinsic FunctionName="isnull"><ScalarOperator><Convert DataType="nvarchar" Length="8000" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader]" Column="tagname"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="N''"></Const></ScalarOperator></Intrinsic></ScalarOperator></Convert></ScalarOperator></Convert></ScalarOperator></Convert></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(128)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Convert DataType="nvarchar" Length="20" Style="0" Implicit="1"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="LT"><ScalarOperator><Intrinsic FunctionName="datalength"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Intrinsic FunctionName="isnull"><ScalarOperator><Convert DataType="nvarchar" Length="8000" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader]" Column="tagname"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="N''"></Const></ScalarOperator></Intrinsic></ScalarOperator></Convert></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(128)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Then><Else><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Intrinsic FunctionName="isnull"><ScalarOperator><Convert DataType="nvarchar" Length="8000" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader]" Column="tagname"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="N''"></Const></ScalarOperator></Intrinsic></ScalarOperator></Convert></ScalarOperator></Else></IF></ScalarOperator></Convert></ScalarOperator></Then><Else><ScalarOperator><Convert DataType="nvarchar" Length="20" Style="0" Implicit="1"><ScalarOperator><Convert DataType="sql_variant" Style="0" Implicit="1"><ScalarOperator><Convert DataType="nvarchar" Length="128" Style="0" Implicit="1"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Intrinsic FunctionName="isnull"><ScalarOperator><Convert DataType="nvarchar" Length="8000" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader]" Column="tagname"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="N''"></Const></ScalarOperator></Intrinsic></ScalarOperator></Convert></ScalarOperator></Convert></ScalarOperator></Convert></ScalarOperator></Convert></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="9" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="4011" EstimatedTotalSubtreeCost="66.0017" Parallel="0" EstimateRebinds="65" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader]" Column="tagname"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="4" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="10" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="6.6157e-05" Parallel="0" EstimateRebinds="0" EstimateRewinds="65" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="4" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><ConstantScan></ConstantScan></RelOp><RelOp NodeId="11" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="1" EstimateIO="0" EstimateCPU="1.00002" AvgRowSize="4011" EstimatedTotalSubtreeCost="66.0013" Parallel="0" EstimateRebinds="65" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader]" Column="tagname"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="4" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[XML Reader]" Column="tagname"></ColumnReference></DefinedValue></DefinedValues><Object Table="[XML Reader]"></Object><ParameterList><ScalarOperator ScalarString="[@xml]"><Identifier><ColumnReference Column="@xml"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="(15)"><Const ConstValue="(15)"></Const></ScalarOperator><ScalarOperator ScalarString="XML Reader with XPath filter.[id]"><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="XML Reader with XPath filter.[id]"><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator></ParameterList></TableValuedFunction></RelOp></NestedLoops></RelOp></StreamAggregate></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></Update></RelOp><ParameterList><ColumnReference Column="@xml" ParameterDataType="xml" ParameterRuntimeValue="N'<Fruits><Apple/><Banana/><Orange/><Pear/></Fruits>'"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |