时间戳

首页 > 解决方案 > T-SQL - XML 路径子句不返回结果集

sql-server - T-SQL - XML 路径子句不返回结果集

问题描述

感谢您的答复。不幸的是，您的代码对我没有帮助。基本上，我想从下面的查询中获取 xml 结果集，并希望以 html 表格格式显示它。然后我想通过电子邮件将数据集发送给客户端。下面是我为此编写的存储过程。EXEC dbo.usp_SFDCGradeComparison

Alter PROCEDURE dbo.usp_SFDCGradeComparison
AS
Begin
Drop table if exists #GradeREport
Select 
GR.EnrollMent EnrollMent_Year_New__c
,GR.SCH_Id SCH_Id
,GR.Name SchoolName
,GR.Reporting__c
,SY.SchoolYear SchoolYear
,SY.SchoolID SchoolID 
, SY.CTEGrades 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'
And ((RTRIM(LTRIM(REPLACE(SY.CTEGrades,',',''))) IS not NULL ` When I execute the script, it returns the rows in SSMS as I mentioned below. Where as in XML Path its not returning the same result set.

EnrollMent SCH_Id Name Reporting__c SchoolYear SchoolID CTEGrades Comments 2018-2019 1056 ABC School 6;7;8;9;10;11;12 NULL NULL NULL NOT Matched 2018-2019 2355 XYZ School 8;9 NULL NULL NULL NOT Matched 2018-2019 1555职业生涯 9;10; NULL NULL NULL 不匹配 2018-2019 1777 商学院 9;10; NULL NULL NULL 不匹配 2018-2019 15555 佛罗里达商学院 9;10; NULL NULL NULL 不匹配 2018-2019 2589 威斯康星商学院 9;10; NULL NULL NULL 不匹配 2018-2019 9999 HS Online Program 9;10; NULL NULL NULL 不匹配

我的预期输出

2018-20191056>ABC School9;10;11;12不匹配 2018-2019>2355>XYZ School8;9;10;不匹配

但我的输出是

这里` OR RTRIM(LTRIM(REPLACE(GR.Reporting__c,';',''))) IS not NULL)) order by SCH_Id, SchoolID For xml Path('tr'), type,root('root')

设置无计数

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

set @body = 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(30)) + '</td><td>' + CAST(s.Reporting__c AS VARCHAR(80)) + '</td> 
 <td>' + CAST(s.SchoolYear AS VARCHAR(120)) + '</td><td>' + 
 CAST(s.SchoolID AS VARCHAR(200)) + '</td><td>' + CAST(s.CTEGrades AS 
 VARCHAR(80)) + '</td><td>' + CAST(s.Comments AS VARCHAR(80)) + '</td>' 
 from ( Select 
            GR.EnrollMent 
            ,GR.SCH_Id 
            ,GR.Name 
            ,GR.Reporting__c 
            ,SY.SchoolYear 
            ,SY.SchoolID 
            , SY.CTEGrades 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'
            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') ) as nvarchar(max) ) select @body print @body

IF @body IS NOT NULL -- 找到记录，发送电子邮件。BEGIN --SELECT @body set @body = '
' + 'EnrollMent_Year_New__cSCH_IdSchool NameReporting__cSchoolYearSchoolIDCTEGradesComments ' + replace( replace( @body, '<', '<' ), '>', '>' )
+ '
' 打印@body EXEC msdb .dbo.sp_send_dbmail @profile_name = @profilename，@recipients = @to，@subject = @subject，@body = @body，@body_format ='HTML'

END ELSE BEGIN PRINT @SuccessMessage END END

标签： sql-servertsqlfor-xml-path

解决方案

确保您拥有有效的 XML 并通知 SQL 引擎您的数据类型。

  1. 有效的 XML：您<td>在开始时缺少一个开始标签。
  2. 数据类型：将字符串值转换为XML数据类型。

我没有要处理的数据#temp，因此我S.*在示例中替换为字段名称字符串。

SELECT
  CAST(      -- cast to XML data type (part 1)
  '<td>' +   -- add missing opening tag
  CAST('EnrollMent' AS varchar(30)) +'</td><td>' + 
  CAST('SCH_Id' AS varchar(30)) + '</td><td>' + 
  CAST('SchoolName' AS varchar(100)) + '</td><td>' + 
  CAST('Reporting_c' AS varchar(80)) + '</td><td>' + 
  CAST('SchoolYear' AS varchar(30)) + '</td><td>' + 
  CAST('SchoolID' AS varchar(30)) + '</td><td>' + 
  CAST('CTEGrades' AS varchar(80)) + '</td><td>' + 
  CAST('Comments' AS varchar(80)) + '</td>'
  AS XML)    -- cast to xml data type (part 2)
FOR XML PATH('row'), TYPE, ROOT('root')

这给了我：

<root><row><td>EnrollMent</td><td>SCH_Id</td><td>SchoolName</td><td>Reporting_c</td><td>SchoolYear</td><td>SchoolID</td><td>CTEGrades</td><td>Comments</td></row></root>

或者使用一些格式：

<root>
   <row>
      <td>EnrollMent</td>
      <td>SCH_Id</td>
      <td>SchoolName</td>
      <td>Reporting_c</td>
      <td>SchoolYear</td>
      <td>SchoolID</td>
      <td>CTEGrades</td>
      <td>Comments</td>
   </row>
</root>

SQL小提琴

如果这不是您想要实现的目标，请使用预期输出更新您的问题。

推荐阅读