how to use sql for xml path('') but retain carriage returns

Question

i have the following piece of code

select
    (
    select cast(Narrative as Varchar(max)) + char(13) 
    from officeclientledger 
    where ptmatter=$matter$ and ptTrans=4 
    for xml path (''), type
    )

which deliver results such as:

Cwm Taff NHS Trust Medical Records ; Dr Purby Medical Records ; (excluding spaces, needed to insert for the purpose of asking the question)

however, is there a way of delivering the results such as

Cwm Taff NHS Trust Medical Records

Dr Purby Medical Records

Answer 1

This can't be done using the results to grid functionality in SSMS. However, if you use the results to text option and replace your CHAR(13) with CHAR(10), you'll get the desired results.

Answer 2

I am hitting the same problem and i can't use Char(10) because the receiving system expect Char (13). So I used below query to replace the value in my result and it worked.

SELECT Coloumn = REPLACE(@v_Get_UPC_List, '
', CHAR(13))