How to get a specific Count element in an XML variable

Question

Consider this XML:

<Employees> <Person> <ID>1000</ID> <Name>Nima</Name> <LName>Agha</LName> </Person> <Person> <ID>1001</ID> <Name>Ligha</Name> <LName>Ligha</LName> </Person> <Person> <ID>1002</ID> <Name>Jigha</Name> <LName>Jigha</LName> </Person> <Person> <ID>1003</ID> <Name>Aba</Name> <LName>Aba</LName> </Person> </Employees> 

I declare an XML variable and assign this XML to it. How can I get the number of ID elements in this XML variable using Sql Server 2008 (TSQL)?

Answer 1

try the following:

 declare @xmlvar XML; set @xmlvar ='<YOUR XML>'; select @xmlvar.value('count(/Employees/Person/ID)', 'INT') AS 'Count' 

Answer 2

 SELECT @XMLVariable.value('count(/Employees/Person/ID)', 'int') AS IDCount