Concatenating and decoding strings from a one-to-many table in SQL

Let's say you have two tables in SQL Server: Person and PersonNotes. PersonNotes is a one-to-many table of notes about that Person.  Here's the SQL for the tables:

create table Person (PersonId int not null)
insert Person (PersonId) values (1)

create table PersonNotes (PersonId int not null, Note varchar(max))
insert PersonNotes (PersonId, Note) values (1, 'note <1>')
insert PersonNotes (PersonId, Note) values (1, '&amp; note #2')
insert PersonNotes (PersonId, Note) values (1, '&amp; finally, note <3>')

And let's say you need to grab all those PersonNotes rows and concatenate them into one string per Person row.  You have a couple options: 1) create a custom function, 2) write a cursor, or 3) use T-SQL's FOR XML trick.  I'll focus on the last one n this post.

Using FOR XML is kind of lame, since it's really meant for something else, but it works pretty well to pull all the strings across rows into one value:

select Person.PersonId, (select PersonNotes.Note + ' '
                        from PersonNotes
                        where PersonNotes.PersonId = Person.PersonId
                        for xml path('')) AS Notes
from Person  
where Person.PersonId = 1

Here's the SQL Fiddle for this table setup and query.  Running this will get you these results:

note <1> & note #2 & finally, note <3>

The problem is, putting it in XML means you get lots of "<", ">", and "&" where the strings have been encoded.  That's what you want for XML, but it's not usually what you want for a string.

But if we use a little XPATH to pull the first node from that resulting XML, we do get the decoded values again:

select Person.PersonId, (select PersonNotes.Note + ' '
                        from PersonNotes
                        where PersonNotes.PersonId = Person.PersonId
                        for xml path(''), root, type).value('/', 'varchar(max)') AS Notes
from Person  
where Person.PersonId = 1

Here's the SQL Fiddle for the tables setup and query with the extra XPATH casting back to varchar(max).  Running this will get you these results:

note <1> &amp; note #2 &amp; finally, note <3>

And we're happy!