TheGoToGuy’s Blog

SQL XML Node Count 21 August 2008

The other day a colleague of mine asked for some help with getting a count of child nodes within a specified xml node retrieved from an xml column in a SQL Server 2005 (will also apply to SQL Server 2008 as well) database. While it’s a very simple task I figured it might be helpful to some other folks so decided to make this post.

Given the following xml stored in a column called “xmlcol” within a table called “testtable”: 

<Root>
   <Node1>
     <Node2a/>
     <Node2b/>
   </Node1>
</Root>

Say you want to get the number of nodes below Node1, you could use a query like the one below:

select
xmlcol.query(‘count(/Root/Node1/*)’)
from testtable

That will obviously return “2”. Easy huh? I’d suggest playing around with it and the xml to get a better idea of how it works. Also look up some info in BOL or Google about XQuery.

Hope you found this useful.

Take care,
TheGoToGuy