June 24, 2015

I have this Sql table setup in Sql Server that contains a XML field and I need to count the number of times a xml node appears in the xml. In practice, it could be the table contains user data and the xml contains a list of cities the user has visited and we need to find out how many cities the user has visited.

Here is an example table schema.


Here is an example xml file.

  1. <cities>
  2.   <city name="Sydney" />
  3.   <city name="Canberra" />
  4.   <city name="Melbourne" />
  5. </cities>

And here is the Sql Query to get the number of times the "city" node appears.

  1. SELECT TOP 1000 [Email]
  2.       ,[CitiesVisited]
  3.       ,[CitiesVisited].value('count(/cities/city)', 'int') as total
  4.   FROM [PracDB].[dbo].[Table_1]


Reference: Shahed Kazi at AspNetify.com