Concatenating text from multiple rows into a single text string using XML PATH

Question

I came across this block of code that I'm using to concatenating elements from multiple rows into one row using XML Path. But the problem that I'm facing is that I want to break the line of the concatenated string using <br> to make them appear one on top of each other when it renders on the web browser. But it is not being able to identify the line break.

Here is my code

SELECT 
    PO.OrderDate,
    (SELECT CHAR(10) + PS.ProductName + '</br>'  
     FROM tbl_OrderInfo AS OI 
     INNER JOIN tblProductEntry_Stock AS PS ON PS.ProductCode = OI.ProductCode
     WHERE OI.GroupCode = PO.OrderCode  
     FOR XML PATH('')) AS ProductList,
    PO.TotalProductPrice + PO.DeliveryCharge AS TotalPrice,
    PO.TotalProductQuantity
FROM 
    tblProductOrder AS PO
WHERE 
    PO.ShopCID = @MerchantId 
    AND CONVERT(date, PO.OrderDate) BETWEEN @FromDate AND @ToDate

Answer 1

You can use CHAR(13) + CHAR(10) to introduce a new line in the resultset.

char(13) is carriage return
char(10) is line feed

 SELECT PO.OrderDate,( SELECT PS.ProductName + CHAR(13) + CHAR(10)  FROM tbl_OrderInfo as OI 
        INNER JOIN tblProductEntry_Stock as PS ON PS.ProductCode = OI.ProductCode
        WHERE OI.GroupCode=PO.OrderCode  FOR XML PATH('')
        ) as ProductList,PO.TotalProductPrice+PO.DeliveryCharge as TotalPrice,PO.TotalProductQuantity
        FROM tblProductOrder as PO
        WHERE PO.ShopCID=@MerchantId AND CONVERT(date,PO.OrderDate) BETWEEN @FromDate AND @ToDate