Try putting the FOR XML PATH stringinator into a CROSS APPLY, where it can crunch up the rows of PSFDOC separately from the rest of the query. You might not even need the DISTINCT:
;WITH CTE AS (
SELECT --DISTINCT
LTRIM(RTRIM(P.IncidentIDRef)) AS MODIDREF,
P.JurisID,
P.EntDttm AS CREATEDDTTM,
P.DIDCU AS CREATEDPFIDREF,
'INC' AS MODCODE,
'C' RECTYPE,
x.Comment
FROM PSDOCI P
CROSS APPLY (
SELECT COMMENT = REPLACE(STUFF(
(SELECT '</P>' + C.FDNARR
FROM PSFDOC C
WHERE C.FDDOC# = P.DIDOC#
ORDER BY C.FDDOC#, C.FDLINE FOR XML PATH(''))
, 1, 10, ''), '</P>', '</P>')
) x
--JOIN PSFDOC T
--ON P.DIDOC# = T .FDDOC#
WHERE P.DIFILE = 'PLINCD'
AND (NOT P.DIDESC IN ('cad system narrative', 'dispatch narrative'))
AND P.DIDESC NOT LIKE 'Converted Cad Comment%'
)
SELECT
*,
CAST(CTE.COMMENT AS NVARCHAR(MAX)) AS COMMENT1,
ROW_NUMBER() OVER (PARTITION BY CTE .JURISID, CTE .MODIDREF ORDER BY CTE .JURISID, CTE .MODIDREF) - 1 AS SEQNO
FROM CTE
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden