SQL Server 2005 XML Query

Question

I'm new to querying XML datatype in SQL Server 2005. Anyone can help me create a query for my requirement? Here's a scenario of my column.

Column Name: Cabinet

/*Row 1 XML Data*/
<shelf>
 <box>
   <item type="pencil" color="blue"/>
   <item type="pencil" color="red"/>
   <item type="paper" color="white"/>
   <item type="ribbon" color="red"/>
 </box>
<shelf>

/*Row 2 XML Data*/
<shelf>   
  <item type="pencil" color="yellow"/>
  <item type="can" color="blue"/>
  <item type="scissor" color="yellow"/>
<shelf>

Desired Output:
4
3

I want to count the number of "item" nodes regardless of its type & color. Thanks in advance.

Answer 1

Have a look at this (Full working example)

DECLARE @Table TABLE(
        Cabinet XML
)

INSERT INTO @Table SELECT
'<shelf> 
 <box> 
   <item type="pencil" color="blue"/> 
   <item type="pencil" color="red"/> 
   <item type="paper" color="white"/> 
   <item type="ribbon" color="red"/> 
 </box> 
</shelf>'

INSERT INTO @Table SELECT
'<shelf>    
  <item type="pencil" color="yellow"/> 
  <item type="can" color="blue"/> 
  <item type="scissor" color="yellow"/> 
</shelf>'

SELECT  *,
        Cabinet.query('count(//item)').value('.','int')
FROM    @Table