DECLARE @xml XML
set @xml ='<MVTs>
<MVT>
<TestID>1</TestID>
<GroupName>Control</GroupName>
<Campaigns>
<Utma>1.631356665.1317314344.1317314344.1317314344.1</Utma>
<Utmv />
<Utmz>1.1317314344.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none)</Utmz>
</Campaigns>
</MVT>
<MVT>
<TestID>1</TestID>
<GroupName>Control23</GroupName>
<Campaigns>
<Utma>1.631356665.1317314344.1317314344.1317314344.1</Utma>
<Utmv />
<Utmz>1.1317314344.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none)</Utmz>
</Campaigns>
</MVT>
<MVT>
<TestID>1</TestID>
<GroupName>Hi</GroupName>
<Campaigns>
<Utma>124235435.32535326435.3464645.1</Utma>
<Utmv />
<Utmz>1.1317314344.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none)</Utmz>
</Campaigns>
</MVT>
<MVT>
<TestID>1</TestID>
<GroupName>Someothervalue</GroupName>
<Campaigns>
<Utma>1.631356665.1317314344.1317314344.1317314344.1</Utma>
<Utmv />
<Utmz>1.1317314344.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none)</Utmz>
</Campaigns>
</MVT>
</MVTs>'

SELECT @xml.query('  
    for $i in /MVTs/MVT
    return
     <KPICounts>
		<KPICount GroupName="{$i/GroupName}" />
	 </KPICounts>')

output
----------------------------------------
<KPICounts><KPICount GroupName="Control" /></KPICounts><KPICounts><KPICount GroupName="Control23" /></KPICounts><KPICounts><KPICount GroupName="Hi" /></KPICounts><KPICounts><KPICount GroupName="Someothervalue" /></KPICounts>

declare @table table (Attributes xml, siteID int)

insert @table
select '<Source SourceID="5" Name="TDSR / TPSR" ForeignID="7797722" />
<Categories>
<Category CategoryID="1" Default="Car" Level="2" Type="Category" />
</Categories>
<AdStructures>
<AdStructure Default="Limited Sedan 4D" Type="Trim" />
<AdStructure Default="2007" Type="Year" />
<AdStructure Default="Hyundai" Type="Make" />
<AdStructure Default="Azera" Type="Model" />
<AdStructure Default="Hyundai" Type="SearchMake" />
<AdStructure Default="Azera" Type="SearchModel" />
<AdStructure Default="Limited Sedan 4D" Type="SearchTrim" />
</AdStructures>
<Prices>
<Price Default="21995.0000" Type="Price" />
<Price Default="false" Type="HidePrice" />
<Price Default="false" Type="HideWholeSalesPrice" />
</Prices>
<AdFields>
<AdField Default="43312" Type="Odometer" />
<AdField Default="19598" Type="StockNumber" />
<AdField Default="Used" Type="Status" Value="1" Fr="Occasion" />
</AdFields>
<UpSells />
<Location Type="Ad" Address="" AddressValidationResult="0" City="SARNIA" Latitude="42.960705" Longtitude="-82.380661" Province="ON" Gridx="291613" Gridy="-22703" />
<Contacts>
<Contact Type="Main" Value="" IndividualForeignID="" IndividualSourceID="-2147483648" IndividualRank="-2147483648">
<Location Type="Company" Address="799 CONFEDERATION ST" AddressValidationResult="0" City="SARNIA" Latitude="42.960705" Longtitude="-82.380661" Province="ON" Gridx="291613" Gridy="-22703" />
<Phones>
<Phone Value="18888636890" Type="Main" />
<Phone Value="5193322304" Type="Fax" />
</Phones>
</Contact>
</Contacts>
<Companies>
<Company CompanyID="6399" Name="SARNIA FINE CARS" SourceID="5" ForeignID="ON20080114101005070" Type="Main" />
</Companies>
<CustomerInfo>
<Name>Jocelyn</Name>
<Email>jlepine@jllmanagement.net</Email>
<Phone />
<Comments>test</Comments>
<ListViewSortType />
<PhotoViewSortType />
</CustomerInfo>

<MVTs>

<MVT>
<TestID>1</TestID>
<TestGroup>PROD</TestGroup>
<Campaign>Campaign-A</Campaign>
</MVT>

<MVT>
<TestID>2</TestID>
<TestGroup>BETA</TestGroup>
<Campaign>Campaign-B</Campaign>
</MVT>

<MVT>
<TestID>3</TestID>
<TestGroup>Control</TestGroup>
<Campaign>Campaign-C</Campaign>
</MVT>

</MVTs>',
2 

SELECT Attributes.query(' 
   <KPICounts>
		{for $i in /MVTs/MVT  
			return 
		 <KPICount SiteID="{sql:column("siteID")}" TestGroupID="{$i/TestID}" TestGroup="{$i/TestGroup}"/>
		}
    </KPICounts>
	 ')
from @table

output
------------------------------------
<KPICounts>
  <KPICount SiteID="2" TestGroupID="1" TestGroup="PROD" />
  <KPICount SiteID="2" TestGroupID="2" TestGroup="BETA" />
  <KPICount SiteID="2" TestGroupID="3" TestGroup="Control" />
</KPICounts>

SELECT Attributes.query(' 
   <KPICounts>
		{for $i in /MVTs/MVT  
			return			 
			(<KPICount SiteID="{sql:column("siteID")}" >
			{if ($i/TestGroup="PROD")
			then attribute TestGroupID {xs:double("1")}
			else (if ($i/TestGroup="BETA")
			     then attribute TestGroupID {xs:double("2")}
			     else attribute TestGroupID {xs:double("3")} 
			     )
			}
			{attribute TestGroup {$i/TestGroup}}
			</KPICount>
			)
		}
    </KPICounts>
	 ')
from @table

output
-----------------------------------
<KPICounts>
  <KPICount SiteID="2" TestGroupID="1" TestGroup="PROD" />
  <KPICount SiteID="2" TestGroupID="2" TestGroup="BETA" />
  <KPICount SiteID="2" TestGroupID="3" TestGroup="Control" />
</KPICounts>

quote:

---

Originally posted by gongxia649

Thanks V,
when i execute the select statement without the emaildate i get a wrong result.
if i execute this select statement i like to get a result like this:
Basically value has the count for production, beta or control. Basically production has 2 rows, beta has 4 rows and control has 2 rows.

SELECT groups.query(' <Counts>
{for $i in /MVTs/MVT
return
<KPI col = "{$i/TestGroup}" Value = "{sql:column("cnt")}"/>}
</Counts>')
FROM @table


result i like to have:
<Counts><KPI col="Production" Value="2" /></Counts>
<Counts><KPI col="Production" Value="2" /></Counts>
<Counts><KPI col="Beta" Value="4" /></Counts>
<Counts><KPI col="Control" Value="2" /></Counts>
<Counts><KPI col="Beta" Value="4" /></Counts>
<Counts><KPI col="Beta" Value="4" /></Counts>
<Counts><KPI col="Beta" Value="4" /></Counts>
<Counts><KPI col="Control" Value="2" /></Counts>

---