SQL Server中如何根据存储过程参数正确格式化目标XML

需要编写适配SSRS场景的参数化包装存储过程，调用以XML（实际参数类型为text）为入参的Report_Invoice存储过程，目标入参XML格式如下：

EXEC Report_Invoice @Xml = N'<XML><Params Date="2022-07-05T00:00:00" /><Customer KeyId="19" /><company KeyId="1" /><Department KeyId="1" /><Office KeyId="1" /></XML>'

原有实现通过多表LEFT JOIN配合FOR XML AUTO生成XML，最终输出为多层嵌套结构，不符合根节点下所有业务节点平级、各节点携带对应属性的要求，错误输出结构示例：

<XML>
  <Params Date="2022-07-05T23:16:00">
    <CustomerOffice KeyId="1">
      <Department KeyId="2">
        <company KeyId="3">
          <Client KeyId="4" />
        </company>
      </Department>
    </CustomerOffice>
  </Params>
</XML>

• FOR XML AUTO模式的生成逻辑为：按照FROM/JOIN的表关联顺序，将后续关联表作为前表的子节点嵌套生成，因此必然产生多层嵌套结构，无法输出平级节点。
• 原有实现中做了无意义的单值表JOIN，同时节点别名映射错误（如Office节点被命名为CustomerOffice，Customer节点被错误映射为Client）。

直接使用FOR XML PATH模式通过XPath路径语法直接定义节点和属性位置，无需多余JOIN操作，即可一步生成符合要求的平级结构XML，修正后的完整存储过程代码如下：

CREATE OR ALTER PROCEDURE Report_Statement
    @EndDate SMALLDATETIME = NULL,
    @OfficeFilterBy INT = NULL,
    @DepartmentFilterBy INT = NULL,
    @companyFilterBy INT = NULL,
    @CustomerIdFilterBy INT = NULL
AS
BEGIN
    DECLARE @myXml Xml = (
        SELECT 
            @EndDate AS [Params/@Date],
            @CustomerIdFilterBy AS [Customer/@KeyId],
            @companyFilterBy AS [company/@KeyId],
            @DepartmentFilterBy AS [Department/@KeyId],
            @OfficeFilterBy AS [Office/@KeyId]
        FOR XML PATH(''), TYPE, ROOT('XML')
    )

    DECLARE @myXmlStr NVARCHAR(MAX) = CAST(@myXml AS NVARCHAR(MAX))

    EXEC Report_Invoice @Xml = @myXmlStr
END
GO

代码说明 #

• PATH('')：指定不生成每行数据的外层包裹节点，直接按照列定义的XPath路径输出内容。
• [Params/@Date]类写法：表示生成名为Params的节点，给该节点添加Date属性，属性值为对应变量内容。
• ROOT('XML')：指定所有生成的平级节点统一包裹在<XML>根节点下。

使用测试参数执行：

DECLARE @cDate DATETIME = GETDATE()

EXECUTE [dbo].Report_Statement 
   @EndDate = @cDate,
   @OfficeFilterBy = 1,
   @DepartmentFilterBy = 2,
   @companyFilterBy = 3,
   @CustomerIdFilterBy = 4

生成的XML结构完全匹配要求：

<XML>
  <Params Date="2024-05-20T14:30:00" />
  <Customer KeyId="4" />
  <company KeyId="3" />
  <Department KeyId="2" />
  <Office KeyId="1" />
</XML>

如果需要参数为NULL时不生成对应节点，可额外加ELEMENTS XSINIL参数或通过分支判断拼接节点即可。

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