Блог

В последнее время я занимаюсь синтаксическим анализом xml с использованием vb.net.

По мере того, как я углубляюсь, я понимаю, что существует 2 типа содержимого xml-файлов. Первый тип-это xmls, которые я могу легко преобразовать в набор данных, и я могу легко увидеть их структуру содержимого с помощью визуализатора VS dataset, а второй тип-это xmls, которые содержат одни и те же узлы несколько раз, и я получаю таблицу (xxxxx), которая не может быть дочерней таблицей для себя во вложенных отношениях.

Например, у меня есть следующий xml-файл, представляющий собой план выполнения запроса от SQL Server, и я получаю сообщение об ошибке

Таблица (СкалярОператор) не может быть дочерней таблицей для самой себя во вложенных отношениях

Я хочу получить из XML некоторую конкретную информацию, например, выходные столбцы, которые находятся в ColumnReference » узле.

У меня есть этот код, в котором я загружаю xml-план в локальный файл, и я пытаюсь его проанализировать.

Ошибка возникает в последней строке кода

     Dim v4str As String = _Session.ExecuteSQLScalar(execplan, v4SQLParams)
    Dim file As System.IO.StreamWriter
    file = My.Computer.FileSystem.OpenTextFileWriter("exectmpplan.xml", True)
    file.WriteLine(v4str)
    file.Close()

    Dim exectmpplan As New XmlDocument()
    exectmpplan.Load("exectmpplan.xml")
    Dim xmlnoerdr As New XmlNodeReader(exectmpplan)

    Dim ds As New DataSet()
    ds.ReadXml(xmlnoerdr)
 

XML-это :

 <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.2080.9">
    <BatchSequence>
        <Batch>
           <Statements>
               <StmtSimple StatementText="SELECT A.ItemGID,A.Code,A.fCompanyCode, A.fColorCode, A.fSizeCode, B.KindSite, B.KindWH, B.gid as SiteGid , C.Description from ESMMItemCodes a CROSS JOIN (SELECT GID,KindSite,KindWH FROM ESGOSites where fPersonCodeGID IN (SELECT top 1 fPersonCodeGID FROM ESGOCompany  ) AND KindSite=1 UNION ALL  SELECT GID,KindSite,KindWH FROM ESGOSites where fSiteGID IN (SELECT GID from ESGOSites WHERE fpersonCodeGid in (SELECT top 1 fPersonCodeGID FROM ESGOCompany ) AND KindSite=1)) b LEFT OUTER JOIN ESGOSites c on b.GID=c.GID WHERE a.NumericField1=0" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.110352" StatementEstRows="2927.62" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x155DC656E56E98CB" QueryPlanHash="0x3EF126EF26FDB4FD" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" /><QueryPlan CachedPlanSize="88" CompileTime="25" CompileCPU="25" CompileMemory="2072"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" GrantedMemory="0" MaxUsedMemory="0" /><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="99692" EstimatedPagesCached="49846" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="649288" /><RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Right Outer Join" EstimateRows="2927.62" EstimateIO="0" EstimateCPU="0.0124106" AvgRowSize="249" EstimatedTotalSubtreeCost="0.110352" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="ItemGID" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="Code" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="fCompanyCode" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="fColorCode" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="fSizeCode" /><ColumnReference Column="Union1011" /><ColumnReference Column="Union1012" /><ColumnReference Column="Union1013" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Alias="[c]" Column="Description" /></OutputList><Merge ManyToMany="0"><InnerSideJoinColumns><ColumnReference Column="Union1011" /></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Alias="[c]" Column="GID" /></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[Union1011]=[ESERPDEMOV4].[dbo].[ESGOSites].[GID] as [c].[GID]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Union1011" /></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Alias="[c]" Column="GID" /></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="174" EstimatedRowsRead="174" EstimateIO="0.020162" EstimateCPU="0.0003484" AvgRowSize="127" EstimatedTotalSubtreeCost="0.0205104" TableCardinality="174" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Alias="[c]" Column="GID" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Alias="[c]" Column="Description" /></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Alias="[c]" Column="GID" /></DefinedValue><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Alias="[c]" Column="Description" /></DefinedValue></DefinedValues><Object Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Index="[PK_ESGOSites]" Alias="[c]" IndexKind="Clustered" Storage="RowStore" /></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="2927.62" EstimateIO="0" EstimateCPU="0.0122374" AvgRowSize="146" EstimatedTotalSubtreeCost="0.077428" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="ItemGID" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="Code" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="fCompanyCode" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="fColorCode" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="fSizeCode" /><ColumnReference Column="Union1011" /><ColumnReference Column="Union1012" /><ColumnReference Column="Union1013" /></OutputList><Warnings NoJoinPredicate="1" /><NestedLoops Optimized="0"><RelOp NodeId="3" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="3.06878" EstimateIO="0.0112613" EstimateCPU="0.000107769" AvgRowSize="25" EstimatedTotalSubtreeCost="0.0378612" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1011" /><ColumnReference Column="Union1012" /><ColumnReference Column="Union1013" /></OutputList><MemoryFractions Input="1" Output="1" /><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Union1011" /></OrderByColumn></OrderBy><RelOp NodeId="4" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="3.06878" EstimateIO="0" EstimateCPU="3.06878e-07" AvgRowSize="25" EstimatedTotalSubtreeCost="0.0264922" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1011" /><ColumnReference Column="Union1012" /><ColumnReference Column="Union1013" /></OutputList><Concat><DefinedValues><DefinedValue><ColumnReference Column="Union1011" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></DefinedValue><DefinedValue><ColumnReference Column="Union1012" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindSite" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindSite" /></DefinedValue><DefinedValue><ColumnReference Column="Union1013" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindWH" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindWH" /></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1.15079" EstimateIO="0" EstimateCPU="4.81032e-06" AvgRowSize="25" EstimatedTotalSubtreeCost="0.0100047" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindSite" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindWH" /></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></OuterReferences><RelOp NodeId="7" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1.15079" EstimateIO="0" EstimateCPU="4.81032e-06" AvgRowSize="23" EstimatedTotalSubtreeCost="0.00657128" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOCompany]" Column="fPersonCodeGID" /></OuterReferences><RelOp NodeId="8" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOCompany]" Column="fPersonCodeGID" /></OutputList><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)" /></ScalarOperator></TopExpression><RelOp NodeId="9" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimateRowsWithoutRowGoal="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOCompany]" Column="fPersonCodeGID" /></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOCompany]" Column="fPersonCodeGID" /></DefinedValue></DefinedValues><Object Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOCompany]" Index="[IX_ESGOCompany_fPersonCodeGID]" TableReferenceId="1" IndexKind="NonClustered" Storage="RowStore" /></IndexScan></RelOp></Top></RelOp><RelOp NodeId="10" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1.15079" EstimatedRowsRead="1.15079" EstimateIO="0.003125" EstimateCPU="0.000158266" AvgRowSize="23" EstimatedTotalSubtreeCost="0.00328327" TableCardinality="174" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></DefinedValue></DefinedValues><Object Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Index="[IX_ESGOSites_ESPERF01]" TableReferenceId="1" IndexKind="NonClustered" Storage="RowStore" /><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="fPersonCodeGID" /></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[ESERPDEMOV4].[dbo].[ESGOCompany].[fPersonCodeGID]"><Identifier><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOCompany]" Column="fPersonCodeGID" /></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="12" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1.15079" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00342859" TableCardinality="174" Parallel="0" EstimateRebinds="0.150794" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindSite" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindWH" /></OutputList><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindSite" /></DefinedValue><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindWH" /></DefinedValue></DefinedValues><Object Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Index="[PK_ESGOSites]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" /><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[ESERPDEMOV4].[dbo].[ESGOSites].[GID]"><Identifier><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[ESERPDEMOV4].[dbo].[ESGOSites].[KindSite]=(1)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindSite" /></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)" /></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="15" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="25" EstimatedTotalSubtreeCost="0.0164867" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindSite" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindWH" /></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></OuterReferences><RelOp NodeId="16" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="8.0172e-06" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0131994" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></OuterReferences><RelOp NodeId="17" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1.15079" EstimateIO="0" EstimateCPU="4.81032e-06" AvgRowSize="24" EstimatedTotalSubtreeCost="0.00988303" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></OuterReferences><RelOp NodeId="18" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1.15079" EstimateIO="0" EstimateCPU="4.81032e-06" AvgRowSize="23" EstimatedTotalSubtreeCost="0.00657128" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOCompany]" Column="fPersonCodeGID" /></OuterReferences><RelOp NodeId="19" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOCompany]" Column="fPersonCodeGID" /></OutputList><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)" /></ScalarOperator></TopExpression><RelOp NodeId="20" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimateRowsWithoutRowGoal="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOCompany]" Column="fPersonCodeGID" /></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOCompany]" Column="fPersonCodeGID" /></DefinedValue></DefinedValues><Object Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOCompany]" Index="[IX_ESGOCompany_fPersonCodeGID]" TableReferenceId="2" IndexKind="NonClustered" Storage="RowStore" /></IndexScan></RelOp></Top></RelOp><RelOp NodeId="21" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1.15079" EstimatedRowsRead="1.15079" EstimateIO="0.003125" EstimateCPU="0.000158266" AvgRowSize="23" EstimatedTotalSubtreeCost="0.00328327" TableCardinality="174" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></DefinedValue></DefinedValues><Object Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Index="[IX_ESGOSites_ESPERF01]" TableReferenceId="3" IndexKind="NonClustered" Storage="RowStore" /><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="fPersonCodeGID" /></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[ESERPDEMOV4].[dbo].[ESGOCompany].[fPersonCodeGID]"><Identifier><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOCompany]" Column="fPersonCodeGID" /></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="23" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1.15079" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00330694" TableCardinality="174" Parallel="0" EstimateRebinds="0.150794" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList /><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues /><Object Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Index="[PK_ESGOSites]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" /><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[ESERPDEMOV4].[dbo].[ESGOSites].[GID]"><Identifier><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[ESERPDEMOV4].[dbo].[ESGOSites].[KindSite]=(1)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindSite" /></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)" /></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="24" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1.66667" EstimatedRowsRead="1.66667" EstimateIO="0.003125" EstimateCPU="0.000158833" AvgRowSize="23" EstimatedTotalSubtreeCost="0.00330778" TableCardinality="174" Parallel="0" EstimateRebinds="0.150794" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></DefinedValue></DefinedValues><Object Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Index="[IX_ESGOSites_fSiteGID]" TableReferenceId="2" IndexKind="NonClustered" Storage="RowStore" /><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="fSiteGID" /></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[ESERPDEMOV4].[dbo].[ESGOSites].[GID]"><Identifier><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="26" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="174" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindSite" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindWH" /></OutputList><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindSite" /></DefinedValue><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="KindWH" /></DefinedValue></DefinedValues><Object Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Index="[PK_ESGOSites]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" /><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[ESERPDEMOV4].[dbo].[ESGOSites].[GID]"><Identifier><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESGOSites]" Column="GID" /></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Concat></RelOp></Sort></RelOp><RelOp NodeId="27" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="954" EstimatedRowsRead="954" EstimateIO="0.0224628" EstimateCPU="0.0011279" AvgRowSize="141" EstimatedTotalSubtreeCost="0.025924" TableCardinality="954" Parallel="0" EstimateRebinds="0" EstimateRewinds="2.06878" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="ItemGID" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="Code" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="fCompanyCode" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="fColorCode" /><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="fSizeCode" /></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="ItemGID" /></DefinedValue><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="Code" /></DefinedValue><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="fCompanyCode" /></DefinedValue><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="fColorCode" /></DefinedValue><DefinedValue><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="fSizeCode" /></DefinedValue></DefinedValues><Object Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Index="[PK_ESMMItemCodes]" Alias="[a]" IndexKind="Clustered" Storage="RowStore" /><Predicate><ScalarOperator ScalarString="[ESERPDEMOV4].[dbo].[ESMMItemCodes].[NumericField1] as [a].[NumericField1]=(0.0000000000)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[ESERPDEMOV4]" Schema="[dbo]" Table="[ESMMItemCodes]" Alias="[a]" Column="NumericField1" /></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0.0000000000)" /></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></Merge></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
 

Я нашел расположение схемы в первой строке xml (http://schemas.microsoft.com/sqlserver/2004/07/showplan/sql2019/showplanxml.xsd), а затем использовал xsd.exe инструмент для создания классов. Затем использовал приведенный ниже код для анализа xml

 Option Strict Off
Option Explicit On

Imports System.Xml
Imports System.Xml.Serialization
Public Module Module1
    Const FILENAME As String = "c:temptest.xml"
    Sub Main()
        Dim reader As XmlReader = XmlReader.Create(FILENAME)
        Dim serializer As XmlSerializer = New XmlSerializer(GetType(ShowPlanXML))
        Dim showPlanXml As ShowPlanXML = serializer.Deserialize(reader)

    End Sub