I know that this thread is a week old and Eirikur has already provided a potential solution but I'd thought I'd add some comments + another solution as well. Firstly the XML you are working with is pretty poor in structure and if that is indeed what you have to work with, I'd look to resolve the schema of that first otherwise you are always going to be fighting the xml with some creative querying. Anyway here is another way of approaching the problem by shredding the xml and then using a pivot. This is too based on some assumptions on the xml schema design.

DECLARE @xml XML

SET @xml = '<ProductCategory>1

<Dairy1>Milk</Dairy1>

<Dairy1>yougurt</Dairy1>

<Dairy2>Cheese</Dairy2>

<Dairy3>Butter </Dairy3>

<Dairy>CreamCheese </Dairy>

<Fruit>Apple </Fruit>

<Fruit>Banana </Fruit>

<Fruit1>Orange </Fruit1>

<Fruit1>GrapeFruit</Fruit1>

<Fruit1>Garpes</Fruit1>

<Fruit2>Strawberry</Fruit2>

<Vegetable>Onion </Vegetable>

<others>Walnut</others>

</ProductCategory>';

WITH x AS

(

SELECT PC.c.value('text()[1]', 'varchar(10)') AS 'ProductCategory'

, d.c.value('upper-case(substring(local-name(.),1,1))', 'char(1)') AS 'ProductType'

, d.c.value('text()[1]', 'varchar(100)') AS 'ProductValue'

FROM @xml.nodes('/ProductCategory') PC(c)

CROSS APPLY PC.c.nodes('*') d(c)

)

SELECT ProductCategory, [D] AS 'Dairy', [F] AS 'Fruit', [V] AS 'Vegtables', [O] AS 'Others'

FROM

(

SELECT ROW_NUMBER() OVER (PARTITION BY ProductCategory, ProductType ORDER BY (SELECT NULL)) AS RowId, *

FROM x

) AS Src

PIVOT

(

MAX(ProductValue)

FOR ProductType IN ([D], [F], [O], [V])

) AS Pvt