Can multiple values per attribute be listed on their own line from a SELECT statement?

sql, sql-server, t-sql, xml

Solution

CREATE TABLE #Temp (
    [@name] VARCHAR(200),
    [Value] VARCHAR(200)
)

INSERT INTO #Temp ([@name], [Value]) 
SELECT 'Fruit', 'Apple' UNION
SELECT 'Fruit', 'Orange' UNION
SELECT 'Fruit', 'Grape' UNION
SELECT 'Building', 'house'


Select  [@Name],
(
SELECT    
    [Value]
FROM #Temp t2
where t2.[@Name] = t1.[@Name]
FOR XML  PATH (''), TYPE
) FROM #Temp t1 GROUP by [@Name]
FOR XML PATH ('Produce'), ROOT('ProduceMaster'), TYPE

Problem

I asked a very similar question previously however now I need the same results but with a different input. Sample Code: ``` CREATE TABLE #Temp ( [@name] VARCHAR(200), [Value] VARCHAR(200) ) INSERT INTO #Temp ([@name], [Value]) SELECT 'Fruit', 'Apple' UNION SELECT 'Fruit', 'Orange' UNION SELECT 'Fruit', 'Grape' ``` Select Statement ``` SELECT [@name], [Value] FROM #Temp FOR XML PATH ('Produce'), ROOT('ProduceMaster'), TYPE ``` Result ``` <ProduceMaster> <Produce name="Fruit"> <Value>Apple</Value> </Produce> <Produce name="Fruit"> <Value>Grape</Value> </Produce> <Produce name="Fruit"> <Value>Orange</Value> </Produce> </ProduceMaster> ``` Desired Result ``` <ProduceMaster> <Produce name="Fruit"> <Value>Apple</Value> <Value>Grape</Value> <Value>Orange</Value> </Produce> </ProduceMaster> ```

Original source