Get XML nodes values with a carriage return using SQL [duplicate]

Question

How to get the value of 'note' tag value with a carriage return?
Here is my code:

declare @Input xml='<Root>
  <Addresses>
    <Address>
      <note>AAA</note>
    </Address>
    <Address>
      <note>BBB</note>
    </Address>
    <Address>
      <note>CCC</note>
    </Address>
    <Address>
      <note>DDD</note>
    </Address>    
  </Addresses>
</Root>'

 SELECT CONVERT(nvarchar(max),m.c.query('.//Address/note/text()')) AS Comment   
 FROM @Input.nodes('Root/Addresses') AS m ( c )

I am expecting a result like below instead of 'AAABBBCCCDDD'

AAA
BBB
CCC
DDD

This question is pretty similar and should help, the only difference appears to be the other question wants a comma as the delimiter whereas you require a carriage return. — GarethD
– GarethD, Commented Jun 25, 2015 at 8:00

Andrey Korneyev · Accepted Answer · 2015-06-25 09:42:00Z

2

You can use something like this to get desired result:

declare @Result nvarchar(max)

select @Result = isnull(@Result + char(13) + char(10), '') + m.c.value('.', 'nvarchar(max)')
from @Input.nodes('Root/Addresses/Address/note') as m(c)

select @Result

edited Jun 25, 2015 at 9:42

answered Jun 25, 2015 at 7:57

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pradeep Over a year ago

I was expecting the result as same as GarethD mentioned in the comment. Anyways thank you for your solution.

Andrey Korneyev Over a year ago

@Pradeep oh, I haven't figured out what you need to concatenate your values in one string having newline as delimiter. I've updated my answer with alternative approach - have a look, maybe it will fit your requirement.

Collectives™ on Stack Overflow

Get XML nodes values with a carriage return using SQL [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related