By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #T
(
ID integer IDENTITY,
Fruit nvarchar(10) NOT NULL
);
DECLARE @xml xml =
N'
<Fruits>
<Apple />
<Banana />
<Orange />
<Pear />
</Fruits>
';
SET STATISTICS XML ON;
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);
SET STATISTICS XML OFF;
SELECT
T.ID,
T.Fruit
FROM #T AS T
ORDER BY
T.ID;
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <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="15" CompileCPU="15" 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="6273744"></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((-1110939126),(2),N'#T')"><Intrinsic FunctionName="getidentity"><ScalarOperator><Const ConstValue="(-1110939126)"></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> |
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)
INSERT
INSERT
| Cached plan size | 48 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 67.0162 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 66 |
Statement
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)
Warnings
Type conversion in expression (CONVERT_IMPLICIT(nvarchar(64),[Expr1012],0)) may affect "Cardinality Estimate" in query plan choice.
Table Insert
(Insert)
Cost: 0%
Table Insert
Insert input rows into the table specified in Argument field.
| Physical Operation | Table Insert |
|---|---|
| Logical Operation | Insert |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 4 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0101 (0%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.000066 |
| Estimated Subtree Cost | 67.0162 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 66 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Object
[tempdb].[dbo].[#T]
Predicate
[#T].[Fruit] = RaiseIfNullInsert([Expr1015]),[#T].[ID] = [Expr1016]
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 4 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000066 |
| Estimated Subtree Cost | 67.0061 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 66 |
| Estimated Row Size | 25 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 1 |
Output List
Expr1015
Expr1016
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000066 |
| Estimated Subtree Cost | 67.0061 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 66 |
| Estimated Row Size | 29 B |
| Node ID | 2 |
Output List
Expr1007
Expr1015
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
| Physical Operation | Nested Loops |
|---|---|
| Logical Operation | Inner Join |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 4 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.00034 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0002759 |
| Estimated Subtree Cost | 67.0061 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 66 |
| Estimated Row Size | 29 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 3 |
Output List
Expr1007
Expr1014
Outer References
[XML Reader with XPath filter].id
Sequence Project
(Compute Scalar)
Cost: 0%
Sequence Project
| Physical Operation | Sequence Project |
|---|---|
| Logical Operation | Compute Scalar |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 4 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000053 |
| Estimated Subtree Cost | 1.00406 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 66 |
| Estimated Row Size | 469 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 4 |
Output List
[XML Reader with XPath filter].id
Expr1007
Segment
Cost: 0%
Segment
| Physical Operation | Segment |
|---|---|
| Logical Operation | Segment |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 4 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000013 |
| Estimated Subtree Cost | 1.00406 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 66 |
| Estimated Row Size | 469 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 5 |
Output List
[XML Reader with XPath filter].id
Segment1019
Filter
Cost: 0%
Filter
| Physical Operation | Filter |
|---|---|
| Logical Operation | Filter |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 4 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.00006 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.000056 |
| Estimated Subtree Cost | 1.00406 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 66 |
| Estimated Row Size | 461 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 0 |
| Node ID | 6 |
Output List
[XML Reader with XPath filter].id
Predicate
[@xml] IS NOT NULL
Table-valued function
[XML Reader with XPath filter]
Cost: 1%
Table-valued function
| Physical Operation | Table-valued function |
|---|---|
| Logical Operation | Table-valued function |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 4 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 1.004 (1%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 1.004 |
| Estimated Subtree Cost | 1.004 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 200 |
| Estimated Row Size | 461 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 0 |
| Node ID | 7 |
Output List
[XML Reader with XPath filter].id
Object
[XML Reader with XPath filter]
Stream Aggregate
(Aggregate)
Cost: 0%
Stream Aggregate
Compute summary values for groups of rows in a suitably sorted stream.
| Physical Operation | Stream Aggregate |
|---|---|
| Logical Operation | Aggregate |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 4 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000011 |
| Estimated Subtree Cost | 66.0017 |
| Estimated Number of Executions | 66 |
| Number of Executions | 4 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 21 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 8 |
Output List
Expr1014
Nested Loops
(Left Outer Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
| Physical Operation | Nested Loops |
|---|---|
| Logical Operation | Left Outer Join |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 4 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0003338 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000042 |
| Estimated Subtree Cost | 66.0017 |
| Estimated Number of Executions | 66 |
| Number of Executions | 4 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 4011 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 9 |
Output List
[XML Reader].tagname
Constant Scan
Cost: 0%
Constant Scan
| Physical Operation | Constant Scan |
|---|---|
| Logical Operation | Constant Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 4 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000662 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000012 |
| Estimated Subtree Cost | 0.0000662 |
| Estimated Number of Executions | 1 |
| Number of Executions | 4 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 10 |
Table-valued function
[XML Reader]
Cost: 98%
Table-valued function
| Physical Operation | Table-valued function |
|---|---|
| Logical Operation | Table-valued function |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 4 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 66.0013 (98%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 1.00002 |
| Estimated Subtree Cost | 66.0013 |
| Estimated Number of Executions | 66 |
| Number of Executions | 4 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 4011 B |
| Actual Rebinds | 4 |
| Actual Rewinds | 0 |
| Node ID | 11 |
Output List
[XML Reader].tagname
Object
[XML Reader]
| ID | Fruit |
|---|---|
| 1 | Apple |
| 2 | Banana |
| 3 | Orange |
| 4 | Pear |