How to count occurrences of a node in SQL XML?

Question

I am trying to do a count on the number of occurrences of the "Colors" node but have been so far unsuccessful.

Below is what I have tried so far.

If I have the following logic:

DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Colors>
<Color1>White</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>'

SELECT
count(a.b.value('Colors','varchar(10)')) AS Color1
FROM @MyXML.nodes('SampleXML') a(b)

I get the following error:

Msg 2389, Level 16, State 1, Line 50
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Solution 1

This will count the number of Colors nodes which is 1.

select @MyXML.value('count(/SampleXML/Colors)', 'int')

This will count the number of rows in Colors which is 5.

select @MyXML.value('count(/SampleXML/Colors/*)', 'int')

Solution 2

select @MyXML.value('count(/SampleXML/Colors)', 'INT') AS 'Count'

Comments

[Mikael Eriksson]

Would the result be 1 for your sample XML?

[some_bloody_fool]

@MikaelEriksson Sorry, that was in tehre by mistake, it was failing

[Anshu]

Ok, didn't see the parent /SampleXML at first, but don't think it needed a downvote

[NealWalters]

Note to anyone reading this, the c in count must be lower case! Otherwise you get this error: XQuery [XmlData.xmlDoc.value()]: There is no function '{w3.org/2004/07/xpath-functions}:Count()'