如何从参数中格式化SQL Server中的XML？

Question

我正在尝试制作一个包装器参数化的存储过程，以执行另一个以XML为输入的存储过程（用于SSRS）。

这就是当前运行XML存储过程的方式（@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>'

我已经提出了以下内容，但是我敢肯定我做错了。如何正确获得XML格式化？

CREATE OR ALTER PROCEDURE Report_Statement
    @EndDate SMALLDATETIME = NULL,
    @OfficeFilterBy INT = NULL,
    @DepartmentFilterBy INT = NULL,
    @companyFilterBy INT = NULL,
    @CustomerIdFilterBy INT = NULL
AS
    DECLARE @myXml Xml = (
        SELECT 
            Params.[Date],
            CustomerOffice.KeyId,
            Department.KeyId,
            company.KeyId,
            Customer.KeyId
        FROM
            (SELECT 1 AS ID, @EndDate AS [Date]) AS Params
        LEFT JOIN 
            (SELECT 1 AS ID, @OfficeFilterBy AS [KeyId]) CustomerOffice ON CustomerOffice.ID = Params.ID
        LEFT JOIN 
            (SELECT 1 AS ID, @DepartmentFilterBy AS [KeyId]) Department ON Department.ID = Params.ID
        LEFT JOIN 
            (SELECT 1 AS ID, @companyFilterBy AS [KeyId]) company ON company.ID = Params.ID
        LEFT JOIN 
            (SELECT 1 AS ID, @CustomerIdFilterBy AS [KeyId]) Customer ON Customer.ID = Params.ID
        FOR XML AUTO, TYPE, ROOT('XML'))

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

    EXEC Report_Invoice @Xml = @myXmlStr
GO

测试执行：

DECLARE @cDate DATETIME = GETDATE()

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

生成此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>

Answer 1

我不知道为什么您要使用XML传递参数（在不推荐使用的文本数据类型中），但看起来您只需要for xml Path以及XQUERY每个列名的路径

DECLARE @myXmlStr nvarchar(max) = (
    SELECT
      @EndDate AS [Params/@Date],
      @OfficeFilterBy AS [Office/@KeyId],
      @DepartmentFilterBy AS [Department/@KeyId],
      @companyFilterBy AS [company/@KeyId],
      @CustomerIdFilterBy AS [Customer/@KeyId]
    FOR XML PATH(''), ROOT('XML')
);

EXEC Report_Invoice @Xml = @myXmlStr;