How to count occurrences of a node in SQL XML?
select @MyXML.value('count(/SampleXML/Colors)', 'INT') AS 'Count'
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')