I'm not entirely clear on the relationships between the two tables, but I can see that what you need to do is pivot the data before you convert it to XML.

You need a column for each CriticalCareActivityCode per day, instead of a row per code. That's a pivot operation. Details on how to do that are here: http://msdn.microsoft.com/en-us/library/ms177410(SQL.105).aspx

You need a row per date, and a column per code. I'm not sure how many codes you can have per day. If that's variable, you may need to do a "dynamic pivot". There are many articles online about how to do that. I like the one on Simple-Talk: http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/. That's what I like, but it may or may not be what you need, so don't limit yourself to that article just because I recommend it.