Can I split lines into Elements with For XML?

In this example I'm cribbing from a previous question, My source table has a varchar(max) field that I want to split by line into individual elements with FOR XML as I query it. Given this data-

CREATE TABLE Example
(
    [ID] INT
  , [Body] VARCHAR(Max)
);

INSERT INTO Example ([ID], [Body])
VALUES
        (1, 'This is a test'),
        (2, 'This is another
         test with
         two line breaks'),
        (3, 'This is a test
         with one line break');

I want to produce this XML -

    1
    
        This is a test
    

    2
    
        This is another
        test with
        two line breaks
    

    3
    
        This is a test
        with one line break
     

Can I do this with FOR XML alone, or do I need to get xslt involved?

Use a split string function of your choice and split on CRLF.

select E.ID,
       (
       select S.Item as Line
       from dbo.SplitStrings(E.Body, char(13)+char(10)) as S
       for xml path(''), type
       )
from dbo.Example as E
for xml path('row');

Have a look at the blog post Split strings the right way – or the next best way by Aaron Bertrand for a couple of versions to choose from.

StackExchange Database Administrators Q#94706, answer score: 3