Looking at the XML and the expected results, it is apparent that something is missing in the description;-)
The code below is slightly verbose in order to be self explanatory, note that if column names are dynamic then the whole query has to be dynamic.
This should at least get you started.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TXML 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 BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY (SELECT NULL)
ORDER BY (SELECT NULL)
) AS ELEMENT_RID
,PRODUCTCATEGORY.DATA.value('text()[1]', 'VARCHAR(10)') AS PRODCAT_VAL
,ELEMENT.DATA.value('local-name(.)', 'VARCHAR(50)') AS ELEMENT_NAME
,ELEMENT.DATA.value('.[1]', 'VARCHAR(100)') AS ELEMENT_VALUE
FROM @TXML.nodes('ProductCategory') AS PRODUCTCATEGORY(DATA)
OUTER APPLY PRODUCTCATEGORY.DATA.nodes('*') AS ELEMENT(DATA)
)
,MULTI_GROUP AS
(
SELECT
BD.ELEMENT_RID
,BD.ELEMENT_NAME
,BE.ELEMENT_NAME AS GROUP_NAME
FROM BASE_DATA BD
CROSS APPLY BASE_DATA BE
WHERE BD.ELEMENT_NAME <> BE.ELEMENT_NAME
AND BD.ELEMENT_NAME LIKE BE.ELEMENT_NAME + '%'
)
,ALL_GROUPS AS
(
SELECT DISTINCT
BD.ELEMENT_NAME AS GROUP_NAME
FROM BASE_DATA BD
WHERE BD.ELEMENT_NAME NOT IN
(
SELECT
MG.ELEMENT_NAME
FROM MULTI_GROUP MG
)
)
,NUMBERED_GROUPS AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY (SELECT NULL)
ORDER BY (SELECT NULL)
) AS AG_RID
,AG.GROUP_NAME
FROM ALL_GROUPS AG
)
,COL_ROW_ENUM_SET AS
(
SELECT
BD.ELEMENT_RID
,BD.PRODCAT_VAL
,NG.GROUP_NAME
,NG.AG_RID
,ROW_NUMBER() OVER
(
PARTITION BY NG.GROUP_NAME
ORDER BY BD.ELEMENT_RID
) AS EL_GR_RID
,BD.ELEMENT_VALUE
FROM BASE_DATA BD
OUTER APPLY NUMBERED_GROUPS NG
WHERE CHARINDEX(NG.GROUP_NAME, BD.ELEMENT_NAME,1) = 1
)
,ROWS_AND_COLS AS
(
SELECT DISTINCT
NG.AG_RID AS COL_NO
,CRES.EL_GR_RID AS ROW_NO
FROM NUMBERED_GROUPS NG
OUTER APPLY COL_ROW_ENUM_SET CRES
)
SELECT
MAX(CRES.PRODCAT_VAL) AS ProductCategory
,MAX(CASE WHEN RAC.COL_NO = 1 THEN CRES.ELEMENT_VALUE END) AS COL_01
,MAX(CASE WHEN RAC.COL_NO = 2 THEN CRES.ELEMENT_VALUE END) AS COL_02
,MAX(CASE WHEN RAC.COL_NO = 3 THEN CRES.ELEMENT_VALUE END) AS COL_03
,MAX(CASE WHEN RAC.COL_NO = 4 THEN CRES.ELEMENT_VALUE END) AS COL_04
,MAX(CASE WHEN RAC.COL_NO = 5 THEN CRES.ELEMENT_VALUE END) AS COL_05
,MAX(CASE WHEN RAC.COL_NO = 6 THEN CRES.ELEMENT_VALUE END) AS COL_06
FROM ROWS_AND_COLS RAC
LEFT OUTER JOIN COL_ROW_ENUM_SET CRES
ON RAC.COL_NO = CRES.AG_RID
AND RAC.ROW_NO = CRES.EL_GR_RID
GROUP BY RAC.ROW_NO;
Results
ProductCategory COL_01 COL_02 COL_03 COL_04 COL_05 COL_06
--------------- ------------ ----------- -------- -------- -------- -------
1 Milk Apple Walnut Onion NULL NULL
1 yougurt Banana NULL NULL NULL NULL
1 Cheese Orange NULL NULL NULL NULL
1 Butter GrapeFruit NULL NULL NULL NULL
1 CreamCheese Garpes NULL NULL NULL NULL
1 NULL Strawberry NULL NULL NULL NULL