How to combine nodes and break on new values using for XML in SQL Server

Question

In the below example, I need a FOR XML query to say when the actionswords are the same, then combine the content under one node.

Currently I have this:

   <Actionword actionword="Bangs">
       <Content>Your heavy swing forces something to break off your opponent and snaps past you through the air...</Content>
   </Actionword>
   <Actionword actionword="Bangs">
       <Content>...a crushing overhand strike leaves your foe both broken and defeated.</Content>
   </Actionword>
   <Actionword actionword="Bangs">
       <Content>...a crushing overhand strike leaves your foe both broken and defeated.</Content>
   </Actionword>

(note how there is a <Actionword> node between each <content> node. Even though the action word is the same.

I need this :

<Actionword actionword="Bangs">
    <Content>Your heavy swing forces something to break off your opponent and snaps past you through the air...</Content>
    <Content>...a crushing overhand strike leaves your foe both broken and defeated.</Content>
    <Content>...a crushing overhand strike leaves your foe both broken and defeated.</Content>
</Actionword>

My source table is denormalized and stores a new row for each distinct content.

The table structure is simply two fields, Actionword (nvarchar(max)) and [Content] (nvarchar(max)).

It would be extremely helpful if you would at least post the table structure (table and column names, column datatypes), some sample data, and what T-SQL you have already as a starting point! — marc_s
The table structure is simply two fields, Actionword (nvarchar(max)) and [Content] (nvarchar(max)). — Mark Scott

Mikael Eriksson Mikael Eriksson · Accepted Answer · 2015-03-08T14:21:27

You should do a group by on actionword in the main query and get the Content nodes in a correlated sub-query in the column list correlated on actionword.

select T1.Actionword as [@actionword],
       (
       select T2.Content
       from dbo.YourTable as T2
       where T1.Actionword = T2.Actionword
       for xml path(''), type
       )
from dbo.YourTable as T1
group by T1.Actionword
for xml path('Actionword');

How to combine nodes and break on new values using for XML in SQL Server

1 Answers