in sql server how to get a column values with `<br>` separate

In sql server how can I get a column's values with <br> separating them?

Here I am getting with comma separated, but how can I get <br/> in html in sql server?

SELECT
  STUFF(
    (
    SELECT
      ',' + cast(Citation_Id as nvarchar(500))
    FROM tollplus.violated_trips开发者_开发百科
    FOR XML PATH('')
    ), 1, 1, ''
  ) As CitationId

---

try this:

SELECT
    STUFF(
             (SELECT
                  '<br/>' + cast(Citation_Id as nvarchar(500))
                  FROM tollplus.violated_trips
                  FOR XML PATH(''), TYPE
             ).value('.','varchar(max)')
             ,1,5, ''
         ) AS Citation_Id

---

To avoid <br /> appearing as <br />

SELECT  (     
        SELECT CASE
                 WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN ''
                 ELSE '<br />'
               END + cast(Citation_Id as nvarchar(500))
        FROM   tollplus.violated_trips
        FOR XML PATH(''), TYPE  
        ).value('.','nvarchar(max)')  As CitationId 

Although actually if CitationId itself does not contain any problematic characters the following may be more performant if the estimated costs in the execution plan can be believed.

SELECT
  REPLACE(
    (
        SELECT CASE
                 WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN ''
                 ELSE ','
               END + CAST(Citation_Id AS NVARCHAR(500))
        FROM   violated_trips
        FOR XML PATH('')  
    ), ',', '<br />'
  ) AS CitationId