T-SQL XML value loses new line formatsI have XML file loaded into SQL server. I query this file to extract the nodes with value.The problem is the new line characters are lost while selection. How to retain formatting so that when I display the text on the web, it not appear messy without line breaks.See text and screenshots for details
T-SQL code:\[code\]declare @Text xml ;set @Text= '<?xml version="1.0" encoding="utf-8"?><topic> <L1> <Subject>Subject text</Subject> <Details> Story Details are This is paragraph Text after After two line breaks Text after After two line breaks </Details> </L1></topic>';with t as (select @Text [xmlcolumn])--select * from tSELECT x.a.value('(Subject)[1]','nvarchar(max)') as [Subject], x.a.value('(Details)[1]','nvarchar(max)') as [Details]FROM tcross applyt.xmlcolumn.nodes('//L1') x(a)\[/code\]