CODEWITHSUNDEEP
sqlxmlssms
hashbyte
hashbyte

Reputation: 121

SQL getting leaf nodes of XML

I am trying to parse xml data for any leaf nodes It is structured like this:

<xmlPlan>
    <myOp id='0' usage='0.75'>
        ....
        <myOp id='2' usage='0.45'>
            ...
        </myOp>
        <myOp id ='3' usage='0.30'>
            ...
        </myOp>
    </myOp>
    <myOp id='1' usage='0.35'>
        ...
    </myOp>
</xmlPlan>

I would need to select leaf nodes 1 2 and 3 and manipulate the usage numbers within those nodes. Im using this to pull the xml from the db:

DECLARE @queryID INT = 1 

/* get xml with queryID */
DECLARE @xml XML
SET @xml = (SELECT TOP 1 myPlan FROM myTable 
                    WHERE stmtID in (@queryID))

Upvotes: 2

Views: 250

Answers (1)

vendettamit
vendettamit

Reputation: 14677

Below will give you the nodes. But your example XML is not valid with attributes a, b, c in the nodes. 

declare @a xml
declare @b xml

create table #t (
 v varchar(max)
)

set @a = ' <rootNode>
    <node>
        <node>
            some data
        </node>
        <node>
            some data
        </node>
    </node>
    <node>
        some data
    </node>
</rootNode>'

Insert into #t(v)
values(convert(varchar(max), @a))

Select @b = v from #t


SELECT c.value('local-name(.)', 'varchar(max)') as 'node'
    , c.value('.', 'varchar(max)') as 'value'
from @b.nodes('/rootNode//*[not(*)] ') as a(c)

drop table #t

Upvotes: 3

Related Questions