By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
| me@you.com |
| erik@your-mom.com |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.8" Build="14.0.3015.40"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT x.c.value('@address', 'VARCHAR(100)') AS [email]
FROM @x.nodes('/xml/customer/email') AS x(c)
WHERE CHARINDEX('@', x.c.value('@address', 'VARCHAR(100)')) > 0" StatementId="1" StatementCompId="3" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="86.8365" StatementEstRows="19.8" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xE92642FF7AAD4BDA" QueryPlanHash="0x1A5C9FAECB95FC96" CardinalityEstimationModelVersion="140"><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="56" CompileTime="0" CompileCPU="0" CompileMemory="936"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(varchar(100),XML Reader with XPath filter.[lvalue],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(varchar(100),XML Reader with XPath filter.[value],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(varchar(100),XML Reader with XPath filter.[lvalue],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(varchar(100),XML Reader with XPath filter.[value],0)"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2258288"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="19.8" EstimateIO="0" EstimateCPU="1.98e-006" AvgRowSize="61" EstimatedTotalSubtreeCost="86.8365" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1012"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1012"></ColumnReference><ScalarOperator ScalarString="[Expr1011]"><Identifier><ColumnReference Column="Expr1011"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="19.8" EstimateIO="0" EstimateCPU="8.2764e-005" AvgRowSize="61" EstimatedTotalSubtreeCost="86.8365" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1011"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" 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="2" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="19.8" EstimateIO="0" EstimateCPU="5.148e-005" AvgRowSize="461" EstimatedTotalSubtreeCost="67.029" 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="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="3" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="66" EstimateIO="0" EstimateCPU="0.00027588" AvgRowSize="513" EstimatedTotalSubtreeCost="67.029" 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="3" 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="Filter" LogicalOp="Filter" EstimateRows="66" EstimateIO="0" EstimateCPU="5.6e-005" 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="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="1"><RelOp NodeId="5" 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="3" 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="[@x]"><Identifier><ColumnReference Column="@x"></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="[@x] IS NOT NULL"><Identifier><ColumnReference Column="ConstExpr1013"><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Column="@x"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Predicate></Filter></RelOp><RelOp NodeId="6" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.1e-006" AvgRowSize="61" EstimatedTotalSubtreeCost="66.0246" Parallel="0" EstimateRebinds="65" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="MIN(CASE WHEN [@x] IS NULL THEN NULL ELSE CASE WHEN datalength(XML Reader with XPath filter.[value])>=(128) THEN CONVERT_IMPLICIT(varchar(100),XML Reader with XPath filter.[lvalue],0) ELSE CONVERT_IMPLICIT(varchar(100),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="@x"></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" Length="100" 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" Length="100" 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="7" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="1.224e-005" AvgRowSize="8045" EstimatedTotalSubtreeCost="66.0246" Parallel="0" EstimateRebinds="65" 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="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="8" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="18" EstimateIO="0" EstimateCPU="1.00036" AvgRowSize="8497" EstimatedTotalSubtreeCost="66.0238" Parallel="0" EstimateRebinds="65" 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="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="3" ActualRewinds="0" ActualEndOfScans="3" ActualExecutions="3"></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="[@x]"><Identifier><ColumnReference Column="@x"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="(7)"><Const ConstValue="(7)"></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="getdescendantlimit(XML Reader with XPath filter.[id])"><Intrinsic FunctionName="getdescendantlimit"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></ParameterList></TableValuedFunction></RelOp><Predicate><ScalarOperator ScalarString="XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1))"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Intrinsic FunctionName="getancestor"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Intrinsic></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></StreamAggregate></RelOp></NestedLoops></RelOp><Predicate><ScalarOperator ScalarString="charindex('@',[Expr1007])>(0)"><Compare CompareOp="GT"><ScalarOperator><Intrinsic FunctionName="charindex"><ScalarOperator><Const ConstValue="'@'"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue=""></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp><RelOp NodeId="9" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.1e-006" AvgRowSize="61" EstimatedTotalSubtreeCost="19.8074" Parallel="0" EstimateRebinds="18.8" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1011"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="2"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1011"></ColumnReference><ScalarOperator ScalarString="MIN(CASE WHEN [@x] IS NULL THEN NULL ELSE CASE WHEN datalength(XML Reader with XPath filter.[value])>=(128) THEN CONVERT_IMPLICIT(varchar(100),XML Reader with XPath filter.[lvalue],0) ELSE CONVERT_IMPLICIT(varchar(100),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="@x"></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" Length="100" 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" Length="100" 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="10" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="1.224e-005" AvgRowSize="8045" EstimatedTotalSubtreeCost="19.8074" Parallel="0" EstimateRebinds="18.8" 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="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="2" ActualExecutions="2"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="11" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="18" EstimateIO="0" EstimateCPU="1.00036" AvgRowSize="8497" EstimatedTotalSubtreeCost="19.8071" Parallel="0" EstimateRebinds="18.8" 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="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="2" ActualRewinds="0" ActualEndOfScans="2" ActualExecutions="2"></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="[@x]"><Identifier><ColumnReference Column="@x"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="(7)"><Const ConstValue="(7)"></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="getdescendantlimit(XML Reader with XPath filter.[id])"><Intrinsic FunctionName="getdescendantlimit"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></ParameterList></TableValuedFunction></RelOp><Predicate><ScalarOperator ScalarString="XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1))"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Intrinsic FunctionName="getancestor"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Intrinsic></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></StreamAggregate></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp><ParameterList><ColumnReference Column="@x" ParameterDataType="xml" ParameterRuntimeValue="N'<xml><customer name="Max"><email address="me@you.com"/></customer><customer name="Erik"><email address="erik@your-mom.com"/></customer><customer name="Brent"><email address="brentcom"/></customer></xml>'"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
| brentcom |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.8" Build="14.0.3015.40"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT x.c.value('@address', 'VARCHAR(100)') AS [email]
FROM @x.nodes('/xml/customer/email') AS x(c)
WHERE x.c.exist('@address[contains(., "@")]') = 0" StatementId="1" StatementCompId="3" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="39.9542" StatementEstRows="37.9141" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x86FC765D05F55B5C" QueryPlanHash="0x8E050FC2667AB359" CardinalityEstimationModelVersion="140"><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="64" CompileTime="0" CompileCPU="0" CompileMemory="1016"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(64),XML Reader with XPath filter.[value],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(varchar(100),XML Reader with XPath filter.[lvalue],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(varchar(100),XML Reader with XPath filter.[value],0)"></PlanAffectingConvert><MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="1024" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantWarning></Warnings><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="576" RequiredMemory="512" DesiredMemory="576" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="16" MaxQueryMemory="594880"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2258288"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="37.9141" EstimateIO="0" EstimateCPU="3.79141e-006" AvgRowSize="61" EstimatedTotalSubtreeCost="39.9542" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1013"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1013"></ColumnReference><ScalarOperator ScalarString="[Expr1012]"><Identifier><ColumnReference Column="Expr1012"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="37.9141" EstimateIO="0" EstimateCPU="0.000158481" AvgRowSize="61" EstimatedTotalSubtreeCost="39.9542" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" 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="2" PhysicalOp="Merge Join" LogicalOp="Left Anti Semi Join" EstimateRows="37.9141" EstimateIO="0" EstimateCPU="0.00572326" AvgRowSize="461" EstimatedTotalSubtreeCost="2.02581" 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="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Merge ManyToMany="0"><InnerSideJoinColumns><ColumnReference Column="Expr1016"></ColumnReference></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="XML Reader with XPath filter.[id]=[Expr1016] AND XML Reader with XPath filter.[id]<=XML Reader with XPath filter.[id] AND [Expr1017]>XML Reader with XPath filter.[id]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1016"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Column="Expr1017"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Residual><RelOp NodeId="3" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="37.9141" EstimateIO="0" EstimateCPU="9.6e-005" AvgRowSize="913" EstimatedTotalSubtreeCost="1.00412" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference><ColumnReference Column="Expr1017"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="1"><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="200" EstimateIO="0" EstimateCPU="2e-005" AvgRowSize="913" EstimatedTotalSubtreeCost="1.00402" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference><ColumnReference Column="Expr1017"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1017"></ColumnReference><ScalarOperator ScalarString="getdescendantlimit(XML Reader with XPath filter.[id])"><Intrinsic FunctionName="getdescendantlimit"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" 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="3" 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="[@x]"><Identifier><ColumnReference Column="@x"></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><Predicate><ScalarOperator ScalarString="[@x] IS NOT NULL AND [@x] IS NOT NULL"><Logical Operation="AND"><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1014"><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Column="@x"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1015"><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Column="@x"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Logical></ScalarOperator></Predicate></Filter></RelOp><RelOp NodeId="6" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="18.9737" EstimateIO="0.0112613" EstimateCPU="0.000226588" AvgRowSize="913" EstimatedTotalSubtreeCost="1.01596" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference><ColumnReference Column="Expr1016"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" 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 Column="Expr1016"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="7" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="18.9737" EstimateIO="0" EstimateCPU="0.000456" AvgRowSize="913" EstimatedTotalSubtreeCost="1.00448" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference><ColumnReference Column="Expr1016"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="8" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="200" EstimateIO="0" EstimateCPU="2e-005" AvgRowSize="8949" EstimatedTotalSubtreeCost="1.00402" Parallel="0" EstimateRebinds="0" 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="Expr1016"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1016"></ColumnReference><ScalarOperator ScalarString="getancestor(XML Reader with XPath filter.[id],(1))"><Intrinsic FunctionName="getancestor"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="9" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="200" EstimateIO="0" EstimateCPU="1.004" AvgRowSize="8497" EstimatedTotalSubtreeCost="1.004" Parallel="0" EstimateRebinds="0" 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="3" 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><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="[@x]"><Identifier><ColumnReference Column="@x"></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><Predicate><ScalarOperator ScalarString="contains(isnull(CONVERT_IMPLICIT(nvarchar(64),XML Reader with XPath filter.[value],0),N''),XML Reader with XPath filter.[lvalue],N'@',NULL) IS NOT NULL AND contains(isnull(CONVERT_IMPLICIT(nvarchar(64),XML Reader with XPath filter.[value],0),N''),XML Reader with XPath filter.[lvalue],N'@',NULL)=(1)"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Intrinsic FunctionName="contains"><ScalarOperator><Intrinsic FunctionName="isnull"><ScalarOperator><Convert DataType="nvarchar" Length="128" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="N''"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="lvalue"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="N'@'"></Const></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="contains"><ScalarOperator><Intrinsic FunctionName="isnull"><ScalarOperator><Convert DataType="nvarchar" Length="128" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="value"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="N''"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="lvalue"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="N'@'"></Const></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></Filter></RelOp></Sort></RelOp></Merge></RelOp><RelOp NodeId="10" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.1e-006" AvgRowSize="61" EstimatedTotalSubtreeCost="37.9283" Parallel="0" EstimateRebinds="36.9141" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1012"></ColumnReference><ScalarOperator ScalarString="MIN(CASE WHEN [@x] IS NULL THEN NULL ELSE CASE WHEN datalength(XML Reader with XPath filter.[value])>=(128) THEN CONVERT_IMPLICIT(varchar(100),XML Reader with XPath filter.[lvalue],0) ELSE CONVERT_IMPLICIT(varchar(100),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="@x"></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" Length="100" 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" Length="100" 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="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="1.224e-005" AvgRowSize="8045" EstimatedTotalSubtreeCost="37.9282" Parallel="0" EstimateRebinds="36.9141" 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="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="12" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="18" EstimateIO="0" EstimateCPU="1.00036" AvgRowSize="8497" EstimatedTotalSubtreeCost="37.9278" Parallel="0" EstimateRebinds="36.9141" 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="1" 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><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="[@x]"><Identifier><ColumnReference Column="@x"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="(7)"><Const ConstValue="(7)"></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="getdescendantlimit(XML Reader with XPath filter.[id])"><Intrinsic FunctionName="getdescendantlimit"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></ParameterList></TableValuedFunction></RelOp><Predicate><ScalarOperator ScalarString="XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1))"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Intrinsic FunctionName="getancestor"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Intrinsic></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></StreamAggregate></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp><ParameterList><ColumnReference Column="@x" ParameterDataType="xml" ParameterRuntimeValue="N'<xml><customer name="Max"><email address="me@you.com"/></customer><customer name="Erik"><email address="erik@your-mom.com"/></customer><customer name="Brent"><email address="brentcom"/></customer></xml>'"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
| me@you.com |
| erik@your-mom.com |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.8" Build="14.0.3015.40"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH X AS
(
SELECT x.c.value('@address', 'VARCHAR(100)') AS [email]
FROM @x.nodes('/xml/customer/email') AS x(c)
)
SELECT *
FROM X
WHERE CHARINDEX('@', [email]) > 0" StatementId="1" StatementCompId="3" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="67.029" StatementEstRows="19.8" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x7DD4F54F8F2B25D6" QueryPlanHash="0xEB30FD6F40D873C2" CardinalityEstimationModelVersion="140"><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="40" CompileTime="0" CompileCPU="0" CompileMemory="696"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(varchar(100),XML Reader with XPath filter.[lvalue],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(varchar(100),XML Reader with XPath filter.[value],0)"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2258288"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="19.8" EstimateIO="0" EstimateCPU="1.98e-006" AvgRowSize="61" EstimatedTotalSubtreeCost="67.029" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1008"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="[Expr1007]"><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="19.8" EstimateIO="0" EstimateCPU="5.148e-005" AvgRowSize="61" EstimatedTotalSubtreeCost="67.029" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="66" EstimateIO="0" EstimateCPU="0.00027588" AvgRowSize="61" EstimatedTotalSubtreeCost="67.029" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" 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="3" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="66" EstimateIO="0" EstimateCPU="5.6e-005" 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="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="1"><RelOp NodeId="4" 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="3" 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="[@x]"><Identifier><ColumnReference Column="@x"></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="[@x] IS NOT NULL"><Identifier><ColumnReference Column="ConstExpr1009"><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Column="@x"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Predicate></Filter></RelOp><RelOp NodeId="5" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.1e-006" AvgRowSize="61" EstimatedTotalSubtreeCost="66.0246" Parallel="0" EstimateRebinds="65" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="MIN(CASE WHEN [@x] IS NULL THEN NULL ELSE CASE WHEN datalength(XML Reader with XPath filter.[value])>=(128) THEN CONVERT_IMPLICIT(varchar(100),XML Reader with XPath filter.[lvalue],0) ELSE CONVERT_IMPLICIT(varchar(100),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="@x"></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" Length="100" 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" Length="100" 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="6" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="1.224e-005" AvgRowSize="8045" EstimatedTotalSubtreeCost="66.0246" Parallel="0" EstimateRebinds="65" 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="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="7" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="18" EstimateIO="0" EstimateCPU="1.00036" AvgRowSize="8497" EstimatedTotalSubtreeCost="66.0238" Parallel="0" EstimateRebinds="65" 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="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="3" ActualRewinds="0" ActualEndOfScans="3" ActualExecutions="3"></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="[@x]"><Identifier><ColumnReference Column="@x"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="(7)"><Const ConstValue="(7)"></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="getdescendantlimit(XML Reader with XPath filter.[id])"><Intrinsic FunctionName="getdescendantlimit"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></ParameterList></TableValuedFunction></RelOp><Predicate><ScalarOperator ScalarString="XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1))"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Intrinsic FunctionName="getancestor"><ScalarOperator><Identifier><ColumnReference Table="[XML Reader with XPath filter]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Intrinsic></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></StreamAggregate></RelOp></NestedLoops></RelOp><Predicate><ScalarOperator ScalarString="charindex('@',[Expr1007])>(0)"><Compare CompareOp="GT"><ScalarOperator><Intrinsic FunctionName="charindex"><ScalarOperator><Const ConstValue="'@'"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue=""></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></ComputeScalar></RelOp><ParameterList><ColumnReference Column="@x" ParameterDataType="xml" ParameterRuntimeValue="N'<xml><customer name="Max"><email address="me@you.com"/></customer><customer name="Erik"><email address="erik@your-mom.com"/></customer><customer name="Brent"><email address="brentcom"/></customer></xml>'"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |