I am having a problem identifying the node position that contains a particular element in a piece of XML.
In the example below I would like to extract the position for each node within the statements section containing the MissingIndexes element. So here there are four nodes within the statements section and nodes 2 and 4 contain the MissingIndexes element.
<BatchSequence> <Batch> <Statements> <StmtSimple> <StatementSetOptions <QueryPlan <RelOp <StmtCond <Then> <Statements> <StmtSimple <StatementSetOptions <QueryPlan <MissingIndexes> <StmtSimple <StatementSetOptions <QueryPlan <RelOp <StmtSimple <StatementSetOptions <QueryPlan <MissingIndexes> </Statements> </Batch>
Is this possible to do in SQL Server? I have a table of execution plans and I would like to extract this information from it. I've looked at the position function but that only works with predicates so I don't think I can use it.
Forgive me if I have used incorrect terminology anywhere; my XML knowledge is quite poor.
Any help appreciated.