Get XML nodes values with a carriage return using SQL

Question

How to get the value of 'note' tag value with a carriage return?
Here is my code:

declare @Input xml='<Root>
  <Addresses>
    <Address>
      <note>AAA</note>
    </Address>
    <Address>
      <note>BBB</note>
    </Address>
    <Address>
      <note>CCC</note>
    </Address>
    <Address>
      <note>DDD</note>
    </Address>    
  </Addresses>
</Root>'

 SELECT CONVERT(nvarchar(max),m.c.query('.//Address/note/text()')) AS Comment   
 FROM @Input.nodes('Root/Addresses') AS m ( c )

I am expecting a result like below instead of 'AAABBBCCCDDD'

AAA
BBB
CCC
DDD

Answer 1

You can use something like this to get desired result:

declare @Result nvarchar(max)

select @Result = isnull(@Result + char(13) + char(10), '') + m.c.value('.', 'nvarchar(max)')
from @Input.nodes('Root/Addresses/Address/note') as m(c)

select @Result