T-SQL中XML Path子句未返回预期结果，需生成HTML表格并邮件发送

Looking at your stored procedure and the issue you're facing, let's break down what's going wrong and how to fix it. The core problem is that your XML Path-generated content doesn't match the results you see in SSMS, which is causing incorrect HTML tables in your email.

Key Issues in Your Current Code #

• Mismatched Column Name: In the subquery used to build @body, you select GR.EnrollMent but then try to reference s.EnrollMent_Year_New__c in the outer query. This column doesn't exist in the subquery, so it returns NULL for that column in your HTML table.
• Redundant XML Output: The initial SELECT ... FOR XML PATH('tr') statement at the top of the procedure is unnecessary—it outputs XML before your variable assignment runs and doesn't contribute to the email body.
• Filtering Gap: Your WHERE clause includes GR.SCH_Id <> '005', which filters out rows where GR.SCH_Id IS NULL (cases where there's a record in #CoreSchoolYear but no match in #SFDC_GradeReport), since NULL <> '005' evaluates to unknown and excludes those rows.
• Manual Escape Handling: Replacing < and > manually works, but using SQL Server's XML value method is more reliable and cleaner.

Corrected Stored Procedure #

ALTER PROCEDURE dbo.usp_SFDCGradeComparison
AS
BEGIN
    SET NOCOUNT ON; -- Move to top to suppress row count messages

    DROP TABLE IF EXISTS #GradeREport;

    DECLARE @profilename varchar(100) = '';
    DECLARE @to varchar(200) = '';
    DECLARE @subject varchar(100) = '';
    DECLARE @bodyhtml varchar(max) = NULL;
    DECLARE @SuccessMessage varchar(80) = 'No record found';

    -- Build HTML table rows using XML Path
    SET @bodyhtml = CAST((
        SELECT 
            td = CAST(s.EnrollMent_Year_New__c AS VARCHAR(30)) + '</td><td>'
                + CAST(s.SCH_Id AS VARCHAR(30)) + '</td><td>'
                + CAST(s.Name AS VARCHAR(100)) + '</td><td>' -- Increased length to avoid truncation
                + CAST(s.Reporting__c AS VARCHAR(80)) + '</td><td>'
                + CAST(ISNULL(s.SchoolYear, '') AS VARCHAR(120)) + '</td><td>' -- Handle NULLs cleanly
                + CAST(ISNULL(s.SchoolID, '') AS VARCHAR(200)) + '</td><td>'
                + CAST(ISNULL(s.CTEGrades, '') AS VARCHAR(80)) + '</td><td>'
                + CAST(s.Comments AS VARCHAR(80)) + '</td>'
        FROM (
            SELECT 
                GR.EnrollMent_Year_New__c -- Fixed column name to match outer query
                ,GR.SCH_Id
                ,GR.Name
                ,GR.Reporting__c
                ,SY.SchoolYear
                ,SY.SchoolID
                ,SY.CTEGrades
                ,CASE 
                    WHEN RTRIM(LTRIM(REPLACE(GR.Reporting__c,';',''))) = RTRIM(LTRIM(REPLACE(SY.CTEGrades,',',''))) 
                    THEN 'Matched' 
                    ELSE 'NOT Matched' 
                END AS Comments
            FROM #SFDC_GradeReport GR
            FULL OUTER JOIN #CoreSchoolYear SY
                ON GR.SCH_Id = SY.SchoolID
                AND RTRIM(LTRIM(REPLACE(GR.Reporting__c,';',''))) = RTRIM(LTRIM(REPLACE(SY.CTEGrades,',','')))
            WHERE 
                (GR.SCH_Id IS NULL OR SY.SchoolID IS NULL)
                AND (GR.SCH_Id <> '005' OR GR.SCH_Id IS NULL) -- Include rows where GR has no match
                AND (
                    RTRIM(LTRIM(REPLACE(SY.CTEGrades,',',''))) IS NOT NULL 
                    OR RTRIM(LTRIM(REPLACE(GR.Reporting__c,';',''))) IS NOT NULL
                )
        ) AS s
        ORDER BY SCH_Id, SchoolID
        FOR XML PATH('tr'), TYPE, ROOT('root')
    ).value('.', 'NVARCHAR(MAX)') AS NVARCHAR(MAX));

    -- Wrap rows in full HTML table structure and send email
    IF @bodyhtml IS NOT NULL
    BEGIN
        SET @bodyhtml = '<table border="1">' 
            + '<tr><th>EnrollMent_Year_New__c</th><th>SCH_Id</th><th>School Name</th><th>Reporting__c</th><th>SchoolYear</th><th>SchoolID</th><th>CTEGrades</th><th>Comments</th></tr>'
            + @bodyhtml 
            + '</table>';

        PRINT @bodyhtml;
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = @profilename, 
            @recipients = @to, 
            @subject = @subject, 
            @body = @bodyhtml, 
            @body_format = 'HTML';
    END
    ELSE
    BEGIN
        PRINT @SuccessMessage;
    END
END

What Changed & Why #

1. Fixed Column Mismatch: Renamed GR.EnrollMent to GR.EnrollMent_Year_New__c in the subquery to ensure the enrollment year value is correctly passed to the HTML table.
2. Removed Redundant Output: Deleted the initial unused SELECT ... FOR XML statement that was cluttering execution results.
3. Adjusted Filter Logic: Modified the GR.SCH_Id check to include rows where #SFDC_GradeReport has no matching record in #CoreSchoolYear.
4. Cleaner NULL Handling: Used ISNULL() to replace NULL values with empty strings, so your HTML table won't show NULL in empty cells.
5. Improved XML Conversion: Used .value('.', 'NVARCHAR(MAX)') to convert XML to a string automatically, eliminating the need for manual escape character replacement.
6. Prevented Truncation: Increased the length of the Name column to avoid cutting off longer school names.

内容的提问来源于stack exchange，提问作者achu