Using SQL Server 2008
XML Data Type For A Column

How can I recursively count all the nodes within a node...

I have a node that is simply //MyNode[@Item="1"]

There are many nodes beneath this one, and nodes within nodes, hence the
recursive part. I want to count them.

I would also like to get a list of the nodes, such as a list of all the
values of @Item.

Help!

Derek Hart wrote:
Well with XPath/XQuery you could do e.g.
count(//MyNode[@Item="1"]//*)
to count all element nodes contained in that MyNode.
But: MS SQL server supports only a very restricted form of XQuery so you
will have to try yourself whether it accepts that expression or complains.

//MyNode[@Item="1"]//@Item
gives you the Item attributes of everything contained in MyNode. But
again I am not sure SQL server will support that expression.


--

Martin Honnen --- MVP Data Platform Development
http://msmvps.com/blogs/martin_honnen/

The Count worked great!

This is the line I am using:
select FileTreePrivatexml.query('//Node//@GUID') from tuser where UserID=186

It gives the error XQuery [tuser.FileTreePrivateXML.query()]: Attribute may
not appear outside of an element

Do you think I am using it how you suggested?

Ultimately, I need to get a list of the @GUID values, and compare that
against a relational table with a join. I was thinking if I could somehow
insert these into a temp table that might to the trick.

Please let me know if you have any thoughts on getting the list in a stored
proc, and if my idea is solid about using a temp table. Might ask for help
on that if I get further here...


"Martin Honnen" <mahotrash (AT) yahoo (DOT) de> wrote