How can I concatenate values for each distinct key in a mappings table?

I'm trying to condense a mappings table into a concatenated XML string. Essentially, I want to take this table:

Old_Key      Old_Value
1            'a'
1            'b'
1            'c'
2            'd'
2            'e'

And insert it so that the values in col2 are turned into an XML string for each value in Col1, like so:

New_Key New_Value
1       <vals><val>a</val><val>b</val><val>c</val><vals>
2           <vals><val>d</val><val>e</val></vals>

My current concatenation code开发者_高级运维 is:

INSERT INTO New_Table (New_Key, New_Value)
SELECT DISTINCT(Old_Key), (SELECT Old_Value AS val FROM Old_Table FOR XML PATH(''), ROOT('vals')) FROM Old_Table

This code doesn't work, since all of the Old_Values are being concatenated together. How can I make sure that only Old_Values that share the same key are concatenated together? Let me know if there's anything else I can do to clarify my situation. Thanks!

Join against the outer Old_Table in the sub query and use group by instead of distinct.

select
  O1.Old_Key,
  (select Old_Value as val
   from Old_Table as O2
   where O1.Old_Key = O2.Old_Key
   for xml path(''), root('vals'), type) as Keys  
from Old_Table as O1
group by O1.Old_Key

Result

Old_Key  Keys
-------  -------------------------------------------------
1        <vals><val>a</val><val>b</val><val>c</val></vals>
2        <vals><val>d</val><val>e</val></vals>