3

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?

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.