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