April 14, 2014

Sql - Count by XML Attribute

I have a Sql table with a XML column and I want to do a count of rows of data grouped by an attribute value of the XML column. We might need these kind of queries when the XML attribute refer to a type of data. For example, I might have country code as AU and US that refers to two different countries where the data came from. Ok, so, how can I do that?

First, we have a sql table with an XML column to hold the data. I will use the same table as in my previous post on how to query csv from XML column. Here is a copy of the example data that I have.

image


To query this table, I will perform the usual sql select operation on the column and get the attribute values and then perform sql count operation from an outer sql query. Here's the script on how I did it.

Sql Script
  1. SELECT src.typeid as [TypeId],
  2.     count(src.typeid) as [Count]
  3. FROM
  4.   (
  5.     SELECT [Tag].value('(/a/@data)[1]', 'nvarchar(50)') as name
  6.         ,[Tag].value('(/a/@type)[1]', 'int') as typeid
  7.     FROM [Employee].[dbo].[Table_2]
  8.   ) src
  9.  
  10.   group by src.typeid

0 comments:

Reference: Shahed Kazi at AspNetify.com