Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
What do you mean by "position"? Are you talking about position of that node within a string of XML? – Jon Seigel Aug 1 '12 at 17:05
I've just noticed that in IE the formatting hasn't appeared correctly but in Chrome it's fine. The code is indented but without the indentations it probably isn't clear what I'm talking about. The schema is here - <schemas.microsoft.com/sqlserver/2004/07/showplan>; and in the example, Statements contains four elements: StmtSimple (line 4), StmtCond (line 8), StmtSimple (line 15), StmtSimple (line 19). The 2nd and 4th of these contain the MissingIndex element so I want to return the values 2 and 4. I don't know if this is correctly referred to as the number or the position of the node. – user10566 Aug 1 '12 at 17:37

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.