SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

8 rows affected

CUSTOMER_CODE	MergedAddress
GEW	3901 Castle Hayne Road Highway 117 N
GEW	1005 2nd Street
GEW	3901 Castle Hayne Road Highway 117 N
GEW	465 McCarthy Road
GEW	3901 Castle Hayne Road Highway 117 N

Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT CUSTOMER_CODE, MergedAddress = STRING_AGG(CUSTOMER_ADDRSS, ' ') WITHIN GROUP (ORDER BY LINE_NBR) FROM dbo.MyTable GROUP BY CUSTOMER_CODE, ADDRESS_INDEX" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0146969" StatementEstRows="5" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xF52B69C5024CDE6D" QueryPlanHash="0xC024C55A2C6609AA" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" 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" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="168"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="1162024" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4595872"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-01-10T19:46:14.24" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000005_22AA2996]" Table="[MyTable]" Schema="[dbo]" Database="[fiddle_cafd3aa553da420e87196affae97963d]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-01-10T19:46:14.24" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_22AA2996]" Table="[MyTable]" Schema="[dbo]" Database="[fiddle_cafd3aa553da420e87196affae97963d]"></StatisticsInfo></OptimizerStatsUsage><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="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="5" EstimateIO="0" EstimateCPU="7.3e-06" AvgRowSize="4014" EstimatedTotalSubtreeCost="0.0146969" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="CUSTOMER_CODE"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="STRING_AGG([fiddle_cafd3aa553da420e87196affae97963d].[dbo].[MyTable].[CUSTOMER_ADDRSS],' ')WITHIN GROUP (ORDER BY [fiddle_cafd3aa553da420e87196affae97963d].[dbo].[MyTable].[LINE_NBR])"><Aggregate Distinct="0" AggType="STRING_AGG"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="CUSTOMER_ADDRSS"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="' '"></Const></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="ADDRESS_INDEX"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="CUSTOMER_CODE"></ColumnReference></GroupBy><RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="8" EstimateIO="0.0112613" EstimateCPU="0.000137494" AvgRowSize="54" EstimatedTotalSubtreeCost="0.0146896" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="CUSTOMER_CODE"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="CUSTOMER_ADDRSS"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="LINE_NBR"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="ADDRESS_INDEX"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="8" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="ADDRESS_INDEX"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="CUSTOMER_CODE"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="LINE_NBR"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="8" EstimatedRowsRead="8" EstimateIO="0.003125" EstimateCPU="0.0001658" AvgRowSize="54" EstimatedTotalSubtreeCost="0.0032908" TableCardinality="8" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="CUSTOMER_CODE"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="CUSTOMER_ADDRSS"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="LINE_NBR"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="ADDRESS_INDEX"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="8" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="8" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="CUSTOMER_CODE"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="CUSTOMER_ADDRSS"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="LINE_NBR"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Column="ADDRESS_INDEX"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></Sort></RelOp></StreamAggregate></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

Microsoft SQL Server 2005 XML Showplan

CUSTOMER_CODE	MergedAddress
GEW	3901 Castle Hayne Road Highway 117 N
GEW	1005 2nd Street
GEW	3901 Castle Hayne Road Highway 117 N
GEW	465 McCarthy Road
GEW	3901 Castle Hayne Road Highway 117 N

Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT CUSTOMER_CODE, MergedAddress = RTRIM((SELECT CUSTOMER_ADDRSS + ' ' FROM dbo.MyTable AS inside WHERE inside.ADDRESS_INDEX = outside.ADDRESS_INDEX ORDER BY LINE_NBR FOR XML PATH(''), TYPE).value(N'./text()[1]', N'varchar(max)')) FROM dbo.MyTable AS outside GROUP BY CUSTOMER_CODE, ADDRESS_INDEX" StatementId="2" StatementCompId="3" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.100322" StatementEstRows="5" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x0BEC7935AAC0D4F1" QueryPlanHash="0xB61C64BB3757B969" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" 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" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="48" CompileTime="15" CompileCPU="15" CompileMemory="664"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(varchar(max),XML Reader with XPath filter.[lvalue],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(varchar(max),XML Reader with XPath filter.[value],0)"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="704" RequiredMemory="640" DesiredMemory="704" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="1162024" GrantedMemory="1024" MaxUsedMemory="32"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4595872"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-01-10T19:46:14.24" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000005_22AA2996]" Table="[MyTable]" Schema="[dbo]" Database="[fiddle_cafd3aa553da420e87196affae97963d]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-01-10T19:46:14.24" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_22AA2996]" Table="[MyTable]" Schema="[dbo]" Database="[fiddle_cafd3aa553da420e87196affae97963d]"></StatisticsInfo></OptimizerStatsUsage><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="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="5" EstimateIO="0" EstimateCPU="5e-07" AvgRowSize="4038" EstimatedTotalSubtreeCost="0.100322" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="CUSTOMER_CODE"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1013"></ColumnReference><ScalarOperator ScalarString="rtrim([Expr1012])"><Intrinsic FunctionName="rtrim"><ScalarOperator><Identifier><ColumnReference Column="Expr1012"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="5" EstimateIO="0" EstimateCPU="2.09e-05" AvgRowSize="4038" EstimatedTotalSubtreeCost="0.100321" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="CUSTOMER_CODE"></ColumnReference><ColumnReference Column="Expr1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="ADDRESS_INDEX"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="5" EstimateIO="0.0112613" EstimateCPU="0.000137454" AvgRowSize="14" EstimatedTotalSubtreeCost="0.0146895" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="CUSTOMER_CODE"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="ADDRESS_INDEX"></ColumnReference></OutputList><MemoryFractions Input="1" Output="0.5"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="896" OutputMemoryGrant="320" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="1"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="CUSTOMER_CODE"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="ADDRESS_INDEX"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="3" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="8" EstimatedRowsRead="8" EstimateIO="0.003125" EstimateCPU="0.0001658" AvgRowSize="14" EstimatedTotalSubtreeCost="0.0032908" TableCardinality="8" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="CUSTOMER_CODE"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="ADDRESS_INDEX"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="8" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="8" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="CUSTOMER_CODE"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="ADDRESS_INDEX"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></Sort></RelOp><RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.0856111" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="Expr1006"></ColumnReference></OuterReferences><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.0604835" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1006"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="[Expr1005]"><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="UDX" LogicalOp="UDX" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-06" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.060483" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><Extension UDXName="FOR XML"><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference></DefinedValue></DefinedValues><UsedUDXColumns><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="LINE_NBR"></ColumnReference></UsedUDXColumns><RelOp NodeId="7" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1.6" EstimateIO="0.0112613" EstimateCPU="0.000101739" AvgRowSize="47" EstimatedTotalSubtreeCost="0.060478" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="LINE_NBR"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><MemoryFractions Input="0.5" Output="0.5"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="8" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="5" ActualRewinds="0" ActualEndOfScans="5" ActualExecutions="5" InputMemoryGrant="704" OutputMemoryGrant="320" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="LINE_NBR"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="8" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1.6" EstimateIO="0" EstimateCPU="8e-07" AvgRowSize="51" EstimatedTotalSubtreeCost="0.003644" Parallel="0" EstimateRebinds="0" EstimateRewinds="4" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="LINE_NBR"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="[fiddle_cafd3aa553da420e87196affae97963d].[dbo].[MyTable].[CUSTOMER_ADDRSS] as [inside].[CUSTOMER_ADDRSS]+' '"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="CUSTOMER_ADDRSS"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="' '"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="9" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1.6" EstimatedRowsRead="8" EstimateIO="0.0032035" EstimateCPU="8.73e-05" AvgRowSize="51" EstimatedTotalSubtreeCost="0.00364" TableCardinality="8" Parallel="0" EstimateRebinds="0" EstimateRewinds="4" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="CUSTOMER_ADDRSS"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="LINE_NBR"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="8" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="5" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="40" ActualEndOfScans="5" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="CUSTOMER_ADDRSS"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="LINE_NBR"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" IndexKind="Heap" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_cafd3aa553da420e87196affae97963d].[dbo].[MyTable].[ADDRESS_INDEX] as [inside].[ADDRESS_INDEX]=[fiddle_cafd3aa553da420e87196affae97963d].[dbo].[MyTable].[ADDRESS_INDEX] as [outside].[ADDRESS_INDEX]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="ADDRESS_INDEX"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="ADDRESS_INDEX"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></TableScan></RelOp></ComputeScalar></RelOp></Sort></RelOp></Extension></RelOp></ComputeScalar></RelOp><RelOp NodeId="10" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.1e-06" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.0251067" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1012"></ColumnReference><ScalarOperator ScalarString="MIN(CASE WHEN [Expr1006] IS NULL THEN NULL ELSE CASE WHEN datalength(XML Reader with XPath filter.[value])>=(128) THEN CONVERT_IMPLICIT(varchar(max),XML Reader with XPath filter.[lvalue],0) ELSE CONVERT_IMPLICIT(varchar(max),XML Reader with XPath filter.[value],0) END END)"><Aggregate Distinct="0" AggType="MIN"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></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><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(128)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Convert DataType="varchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="lvalue"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></Then><Else><ScalarOperator><Convert DataType="varchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="11" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="8045" EstimatedTotalSubtreeCost="0.0251012" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference><ColumnReference Table="[XML Reader with XPath filter]" Column="lvalue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="5" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></TopExpression><RelOp NodeId="12" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateRowsWithoutRowGoal="200" EstimateIO="0" EstimateCPU="2e-05" AvgRowSize="8949" EstimatedTotalSubtreeCost="0.0251007" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference><ColumnReference Table="[XML Reader with XPath filter]" Column="lvalue"></ColumnReference><ColumnReference Column="Expr1011"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1011"></ColumnReference><ScalarOperator ScalarString="0x58"><Const ConstValue="0x58"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="13" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="1" EstimateRowsWithoutRowGoal="200" EstimateIO="0" EstimateCPU="1.004" AvgRowSize="8497" EstimatedTotalSubtreeCost="0.0251002" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference><ColumnReference Table="[XML Reader with XPath filter]" Column="lvalue"></ColumnReference></OutputList><MemoryFractions Input="0" Output="0"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="5" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[XML Reader with XPath filter]" Column="lvalue"></ColumnReference></DefinedValue></DefinedValues><Object Table="[XML Reader with XPath filter]"></Object><ParameterList><ScalarOperator ScalarString="[Expr1006]"><Identifier><ColumnReference Column="Expr1006"></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></ComputeScalar></RelOp></Top></RelOp></StreamAggregate></RelOp></NestedLoops></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT CUSTOMER_CODE, 
 MergedAddress = RTRIM((SELECT CUSTOMER_ADDRSS + ' '
 FROM dbo.MyTable AS inside
 WHERE inside.ADDRESS_INDEX = outside.ADDRESS_INDEX
 ORDER BY LINE_NBR
 FOR XML PATH(''), TYPE).value(N'./text()[1]', N'varchar(max)'))
 FROM dbo.MyTable AS outside 
 GROUP BY CUSTOMER_CODE, ADDRESS_INDEX" StatementId="2" StatementCompId="3" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.100322" StatementEstRows="5" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x0BEC7935AAC0D4F1" QueryPlanHash="0xB61C64BB3757B969" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" 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" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="48" CompileTime="15" CompileCPU="15" CompileMemory="664"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(varchar(max),XML Reader with XPath filter.[lvalue],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(varchar(max),XML Reader with XPath filter.[value],0)"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="704" RequiredMemory="640" DesiredMemory="704" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="1162024" GrantedMemory="1024" MaxUsedMemory="32"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4595872"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-01-10T19:46:14.24" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000005_22AA2996]" Table="[MyTable]" Schema="[dbo]" Database="[fiddle_cafd3aa553da420e87196affae97963d]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-01-10T19:46:14.24" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_22AA2996]" Table="[MyTable]" Schema="[dbo]" Database="[fiddle_cafd3aa553da420e87196affae97963d]"></StatisticsInfo></OptimizerStatsUsage><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="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="5" EstimateIO="0" EstimateCPU="5e-07" AvgRowSize="4038" EstimatedTotalSubtreeCost="0.100322" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="CUSTOMER_CODE"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1013"></ColumnReference><ScalarOperator ScalarString="rtrim([Expr1012])"><Intrinsic FunctionName="rtrim"><ScalarOperator><Identifier><ColumnReference Column="Expr1012"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="5" EstimateIO="0" EstimateCPU="2.09e-05" AvgRowSize="4038" EstimatedTotalSubtreeCost="0.100321" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="CUSTOMER_CODE"></ColumnReference><ColumnReference Column="Expr1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="ADDRESS_INDEX"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="5" EstimateIO="0.0112613" EstimateCPU="0.000137454" AvgRowSize="14" EstimatedTotalSubtreeCost="0.0146895" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="CUSTOMER_CODE"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="ADDRESS_INDEX"></ColumnReference></OutputList><MemoryFractions Input="1" Output="0.5"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="896" OutputMemoryGrant="320" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="1"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="CUSTOMER_CODE"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="ADDRESS_INDEX"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="3" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="8" EstimatedRowsRead="8" EstimateIO="0.003125" EstimateCPU="0.0001658" AvgRowSize="14" EstimatedTotalSubtreeCost="0.0032908" TableCardinality="8" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="CUSTOMER_CODE"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="ADDRESS_INDEX"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="8" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="8" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="CUSTOMER_CODE"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="ADDRESS_INDEX"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></Sort></RelOp><RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.0856111" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="Expr1006"></ColumnReference></OuterReferences><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.0604835" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1006"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="[Expr1005]"><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="UDX" LogicalOp="UDX" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-06" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.060483" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><Extension UDXName="FOR XML"><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference></DefinedValue></DefinedValues><UsedUDXColumns><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="LINE_NBR"></ColumnReference></UsedUDXColumns><RelOp NodeId="7" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1.6" EstimateIO="0.0112613" EstimateCPU="0.000101739" AvgRowSize="47" EstimatedTotalSubtreeCost="0.060478" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="LINE_NBR"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><MemoryFractions Input="0.5" Output="0.5"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="8" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="5" ActualRewinds="0" ActualEndOfScans="5" ActualExecutions="5" InputMemoryGrant="704" OutputMemoryGrant="320" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="LINE_NBR"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="8" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1.6" EstimateIO="0" EstimateCPU="8e-07" AvgRowSize="51" EstimatedTotalSubtreeCost="0.003644" Parallel="0" EstimateRebinds="0" EstimateRewinds="4" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="LINE_NBR"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="[fiddle_cafd3aa553da420e87196affae97963d].[dbo].[MyTable].[CUSTOMER_ADDRSS] as [inside].[CUSTOMER_ADDRSS]+' '"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="CUSTOMER_ADDRSS"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="' '"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="9" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1.6" EstimatedRowsRead="8" EstimateIO="0.0032035" EstimateCPU="8.73e-05" AvgRowSize="51" EstimatedTotalSubtreeCost="0.00364" TableCardinality="8" Parallel="0" EstimateRebinds="0" EstimateRewinds="4" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="CUSTOMER_ADDRSS"></ColumnReference><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="LINE_NBR"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="8" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="5" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="40" ActualEndOfScans="5" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="CUSTOMER_ADDRSS"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="LINE_NBR"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" IndexKind="Heap" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_cafd3aa553da420e87196affae97963d].[dbo].[MyTable].[ADDRESS_INDEX] as [inside].[ADDRESS_INDEX]=[fiddle_cafd3aa553da420e87196affae97963d].[dbo].[MyTable].[ADDRESS_INDEX] as [outside].[ADDRESS_INDEX]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[inside]" Column="ADDRESS_INDEX"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_cafd3aa553da420e87196affae97963d]" Schema="[dbo]" Table="[MyTable]" Alias="[outside]" Column="ADDRESS_INDEX"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></TableScan></RelOp></ComputeScalar></RelOp></Sort></RelOp></Extension></RelOp></ComputeScalar></RelOp><RelOp NodeId="10" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.1e-06" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.0251067" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1012"></ColumnReference><ScalarOperator ScalarString="MIN(CASE WHEN [Expr1006] IS NULL THEN NULL ELSE CASE WHEN datalength(XML Reader with XPath filter.[value])>=(128) THEN CONVERT_IMPLICIT(varchar(max),XML Reader with XPath filter.[lvalue],0) ELSE CONVERT_IMPLICIT(varchar(max),XML Reader with XPath filter.[value],0) END END)"><Aggregate Distinct="0" AggType="MIN"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></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><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(128)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Convert DataType="varchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="lvalue"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></Then><Else><ScalarOperator><Convert DataType="varchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="11" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="8045" EstimatedTotalSubtreeCost="0.0251012" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference><ColumnReference Table="[XML Reader with XPath filter]" Column="lvalue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="5" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></TopExpression><RelOp NodeId="12" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateRowsWithoutRowGoal="200" EstimateIO="0" EstimateCPU="2e-05" AvgRowSize="8949" EstimatedTotalSubtreeCost="0.0251007" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference><ColumnReference Table="[XML Reader with XPath filter]" Column="lvalue"></ColumnReference><ColumnReference Column="Expr1011"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1011"></ColumnReference><ScalarOperator ScalarString="0x58"><Const ConstValue="0x58"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="13" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="1" EstimateRowsWithoutRowGoal="200" EstimateIO="0" EstimateCPU="1.004" AvgRowSize="8497" EstimatedTotalSubtreeCost="0.0251002" Parallel="0" EstimateRebinds="4" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference><ColumnReference Table="[XML Reader with XPath filter]" Column="lvalue"></ColumnReference></OutputList><MemoryFractions Input="0" Output="0"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="5" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[XML Reader with XPath filter]" Column="lvalue"></ColumnReference></DefinedValue></DefinedValues><Object Table="[XML Reader with XPath filter]"></Object><ParameterList><ScalarOperator ScalarString="[Expr1006]"><Identifier><ColumnReference Column="Expr1006"></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></ComputeScalar></RelOp></Top></RelOp></StreamAggregate></RelOp></NestedLoops></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>