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?


No comments:

Post a Comment