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   -

&lt;row&gt;
    &lt;ID&gt;1&lt;/ID&gt;
    &lt;Body&gt;
        &lt;Line&gt;This is a test&lt;/Line&gt;
    &lt;/Body&gt;
&lt;/row&gt;
&lt;row&gt;
    &lt;ID&gt;2&lt;/ID&gt;
    &lt;Body&gt;
        &lt;Line&gt;This is another&lt;/Line&gt;
        &lt;Line&gt;test with&lt;/Line&gt;
        &lt;Line&gt;two line breaks&lt;/Line&gt;
    &lt;/Body&gt;
&lt;/row&gt;
&lt;row&gt;
    &lt;ID&gt;3&lt;/ID&gt;
    &lt;Body&gt;
        &lt;Line&gt;This is a test&lt;/Line&gt;
        &lt;Line&gt;with one line break&lt;/Line&gt;
    &lt;/Body&gt; 
&lt;/row&gt;


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.