Convert common sql column value to xml node

Let's say I have a data set like this:

|Group|Value|
|-----|-----|
|  A  | 123 |
|-----|-----|
|  A  | 234 |
|-----|-----|
|  B  | 123 |
|-----|-----|

And want to convert it into XML like this:

<Groups>
    <Group Name="A">
         <Values>
              <Value>123</Value>
              <Value>234</Value>   
         </Values> 
    </Group>  
    <Group Name="B">
         <Values>
              <Value>123</Value>
         </Values> 
    </Group>    
</Groups>

I've tried using something along the lines of:

   SELECT  
         [Group] AS "@Name"
        ,[Value] AS "Group/Values/Value"
    FROM [Tablename]
    FOR XML PATH('Group'), ROOT('Groups')

But what I'm getting is:

<Groups>
    <Group Name="A">
         <Values>
              <Value>123</Value> 
         </Values> 
    </Group>  
    <Group Name="A">
         <Values>
              <Value>234</Value>   
         </Values> 
    </Group>  
    <Group Name="B">
         <Values>
              <Value>123</Value>
         </Values> 
    </Group>    
</Groups>

How can I combine these nodes based on the data in the first column?

Answers


Test Data
DECLARE @TABLE TABLE([Group] VARCHAR(10), Value INT)
INSERT INTO @TABLE VALUES 
('A',123),
('A',234),
('B',123)
Query
SELECT   t.[Group] AS [@Name]

      ,(SELECT [Value] AS [Value]
        FROM @TABLE
        WHERE [Group] = t.[Group]
        FOR XML PATH(''), ROOT('Values') ,TYPE)
FROM @TABLE t
GROUP BY t.[Group]
FOR XML PATH('Group'), ROOT('Groups')
Result
<Groups>
  <Group Name="A">
        <Values>
              <Value>123</Value>
              <Value>234</Value>
        </Values>
  </Group>
  <Group Name="B">
        <Values>
             <Value>123</Value>
        </Values>
  </Group>
</Groups>

Need Your Help

calculate uptime/downtime for a service

c#

I have a custom solution that checks if a web-site is online, every 1 hour.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.