Sql-server – split lines into Elements with For XML

sql serverxml

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 –

<row>
    <ID>1</ID>
    <Body>
        <Line>This is a test</Line>
    </Body>
</row>
<row>
    <ID>2</ID>
    <Body>
        <Line>This is another</Line>
        <Line>test with</Line>
        <Line>two line breaks</Line>
    </Body>
</row>
<row>
    <ID>3</ID>
    <Body>
        <Line>This is a test</Line>
        <Line>with one line break</Line>
    </Body> 
</row>

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

Best Answer

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.

Related Question